Show the names of all students, their majors, their advisor names and advisor department names in the following manner. List only students minoring in ITEC.
Provide the MySQL commands for the following queries. Make sure that your
queries produce the exact output as shown (row order can be different).
[3] Show the names of all students, their majors, their advisor names and advisor
department names in the following manner. List only students minoring in ITEC.
+--------+-----------------+------------------------------+-------------+------------------------------+
| stuId | student | major | advisor | advisor department |
+--------+-----------------+------------------------------+-------------+------------------------------+
| 100002 | David Hawk | Computer Science | Mary Tran | Computer Science |
| 100006 | Lillian Johnson | Computer
+--------+-----------------+------------------------------+-------------+------------------------------+
2 rows in set
![MariaDB [toyu2]> select * from class;
| classId | courseId | semester | year | facId | room |
-‒‒‒‒+· ------
10000
10001
10002
10003
10004
10005
10006
10007
10008
11000
11001
11002
11003
11004
2000 Fall
2001
Fall
2002 Fall
2020 Fall
2021 Fall
2040
Fall
2041 Fall
2060
Fall
2080 Fall
2000
Spring
2001
Spring
2002 Spring
2020 Spring
2061 Spring
2000 CSCI
2001 CSCI
2002 CSCI
2020 CINF
2021 CINF
2040 ITEC
2041 ITEC
2060 ENGL
2061 ENGL
ACCT
ARTS
CINF
CSCI
ENGL
ITEC
14 rows in set (0.000 sec)
MariaDB [toyu2]> select from course;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corr
MariaDB [toyu2]> select * from course;
| courseId | rubric | number | title
3333
4333
5333
3321
4320
3335
3312
1410
1311
3311
3333
2019
2019
2019
2019
2019
2019
2019
2019
2019
2020
2020
2020
2020
2020
1011 | D241 |
1011 D242
| MATH
7 rows in set (0.000 sec)
1012
D136
1014 | D241 |
1014 | D241 |
1015 D237 |
1019
D217
1929 |
B191 |
1018 | D241 |
1011 | D241 |
1012
D242
1013
D136
1016 D217
1018 B191 |
Data Structures
Design of Database Systems
DBMS
2080 ARTS
2090 | ACCT
11 rows in set (0.002 sec)
MariaDB [toyu2]> select * from department;
| deptCode | deptName
Accounting
BUS
Arts
HSH
Computer Information Systems | CSE
Computer Science
| CSE
English
Information Technology
Mathematics
Introduction to Information Systems
Web Application Development
Database Development
Introduction to Scripting
English I
English II
Hindu Arts
Managerial Accounting
| schoolCode | numStaff |
HSH
CSE
CSE
10
5|
5
12
12
4
7
| credits |
3 |
3 |
3 |
3 |
3 |
3 |
3 |
4 |
ee
بیا بیا بیا بیا بیا بیا بیا د بنا بنا لیا
3 |
3
3](/v2/_next/image?url=https%3A%2F%2Fcontent.bartleby.com%2Fqna-images%2Fquestion%2F373ec5c3-fcc8-4cc5-a897-c792133242cb%2Fa52a3e56-eeb3-4344-aa2a-cf1ce8389e27%2Ff39s6t4n_processed.png&w=3840&q=75)
![MariaDB [toyu2]> select * from student;
| stuId | fname
| 1name
| 100000 | Tony
100001 Mary
100002 | David
100003 | Catherine |
100004
Larry
|
100005 | Linda
100006 | Lillian
100007 | Ben
100008 | Bill
Hawk
Hawk
Hawk
Lim
Johnson
Johnson
Johnson
Zico
| Ching
| King
| 100009 | Linda
| 100111 | Cathy
11 rows in set (0.000 sec)
Johanson
| major | minor | credits | advisor |
1011 |
1011
1012
NULL
1017
1015
1016
NULL
CSCI
CSCI
CSCI
ITEC
ITEC
CINF
CINF
NULL
ARTS
ARTS
| NULL
CINF
CINF
ITEC
CINF
NULL
ENGL
ITEC
NULL
NULL
CSCI
NULL
40
35 |
66
20
66 |
13 |
18 |
16 |
90 |
125 |
NULL
1018
1018](/v2/_next/image?url=https%3A%2F%2Fcontent.bartleby.com%2Fqna-images%2Fquestion%2F373ec5c3-fcc8-4cc5-a897-c792133242cb%2Fa52a3e56-eeb3-4344-aa2a-cf1ce8389e27%2F7wdcbkf_processed.png&w=3840&q=75)
3.
SELECT is used to display all(*) or specified column/columns
table_name.column_name , it is used to specify that column_name is of table table_name
AS is used for alias , in simple words it is used to change display name of column
INNER JOIN is used to join two table based on common column
WHERE is used to specify condition.
Step by step
Solved in 2 steps with 1 images
