Provide the MySQL commands for the following queries. Make sure that your queries produce the exact output as shown (row order can be different). [2] Show the information (id, fname, lname and major program name) of all students enrolled in the class 10003 in the following manner.
Provide the MySQL commands for the following queries. Make sure that your
queries produce the exact output as shown (row order can be different).
[2] Show the information (id, fname, lname and major program name) of all
students enrolled in the class 10003 in the following manner.
+--------+-------+---------+------------------------------+-------------------+
| stuId | fname | lname | major | class 10003 grade |
+--------+-------+---------+------------------------------+-------------------+
| 100000 | Tony | Hawk | Computer Science | C |
| 100002 | David | Hawk | Computer Science | D |
| 100004 | Larry | Johnson | Information Technology | A |
| 100005 | Linda | Johnson | Computer
+--------+-------+---------+------------------------------+-------------------+
![MariaDB [toyu2]> select from grade;
grade | gradePoint |
A
A-
MUTUOŐÓLAAš
B+
B-
C+
C-
1.6667
1.0000
1.3333
0.6667
0.0000
NULL |
NULL |
NULL |
in set (0.001 sec)
MariaDB [toyu2]> select from school;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual tha
MariaDB [toyu2]> select * from school;
| schoolCode | schoolName
Business
Education
D
D+
D-
F
IP
P
WX
4.0000
3.6667
3.0000
3.3333
2.6667
2.0000
2.3333
15 rows
BUS
EDU
HSH
| CSE
rows in set (0.000 sec)
MariaDB [toyu2]> select from student;
stuId | fname
| 1name
100000 | Tony
Hawk
100001
Mary
Hawk
100002 | David
Hawk
100003 Catherine
Lim
Human Sciences and Humanities
Science and Engineering
100004 Larry
100005 Linda
100006 Lillian
100007 Ben
100008 Bill
100009 Linda
100111 Cathy
| major | minor | credits | advisor |
CSCI | CINF
1011 |
CSCI | CINF
1011
CSCI | ITEC
1012
ITEC
NULL
Johnson
ITEC
1017
Johnson CINF
1015
Johnson
CINF
1016
NULL
NULL
1018
1018
Zico
Ching
King
NULL
ARTS
ARTS
Johanson NULL
11 rows in set (0.000 sec)
MariaDB [toyu2]>
CINF
NULL
ENGL
ITEC
NULL
NULL
CSCI
NULL
40
35
66
20
66
13
18
16
90
125](/v2/_next/image?url=https%3A%2F%2Fcontent.bartleby.com%2Fqna-images%2Fquestion%2F373ec5c3-fcc8-4cc5-a897-c792133242cb%2F38ce704a-5ff7-4dc8-bc8e-2ac2dc88a53b%2Fh6gojsd_processed.png&w=3840&q=75)
![MariaDB [toyu2]> show tables;
| Tables_in_toyu2
class
course
department
enroll
faculty
grade
school
student
8 rows in set (0.001 sec)
MariaDB [toyu2]> select * from class;
| classId | courseId | semester | year | facId
2019
1011
2019
1011
2019
1012
D136
2019
1014
D241
2019
1014
D241
2019
1015 D237
2019
1019
D217
2019
1020
B101
2019
1018
D241
2020
1011
D241
2020
1012
D242
2020 |
1013
D136
2020
1016 | D217 |
2020
1918 | B191 |
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
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 corresponds
MariaDB [toyu2]> select * from course;
| courseId | rubric | number | title
2000 CSCI
2001 CSCI
2002 CSCI
2020 CINF
2021 CINF
2040 ITEC
2041 ITEC
2060 | ENGL
2061 ENGL
2080
ARTS
2090 ACCT
3333 | Data Structures
4333
5333
3321
4320
3335
3312
1410
1311
3311
3333
| room |
D241 |
D242
11 rows in set (0.002 sec)
Design of Database Systems
| DBMS
Introduction to Information Systems
Web Application Development
Database Development
| Introduction to Scripting
English I
| English II
Hindu Arts
| Managerial Accounting
| credits |
3
3 |
3
لینا لینا لنا لنا ليل
3
3
3
لا بیا بیا بیا بیا
3 |
4
3
3 |
3 |](/v2/_next/image?url=https%3A%2F%2Fcontent.bartleby.com%2Fqna-images%2Fquestion%2F373ec5c3-fcc8-4cc5-a897-c792133242cb%2F38ce704a-5ff7-4dc8-bc8e-2ac2dc88a53b%2Flnw1gk_processed.png&w=3840&q=75)
The MySQL commands for the following queries are :
1st Command :
CREATE TABLE grade (grade VARCHAR(10), gradePoint VARCHAR(10);
INSERT INTO grade (grade, gradePoint) VALUES ('A', '4.0000'), ('A-', '3.6667'),('B', '3.0000'), ('B+','3.3333'), ('B-','2.6667'), ('C','2.0000'),('C+','2.3333'), ('C-','1.6667'), ('D','1.0000'), ('D+','1.3333'), ('D-','0.6667'), ('F','0.0000'), ('IP', NULL), ('P', NULL),('WX',NULL);
SELECT * FROM grade;
2nd Command :
CREATE TABLE school (schoolCode VARCHAR(50), schoolName VARCHAR(50));
INSERT INTO school (schoolCode, schoolName) VALUES ('BUS', 'Business'),('EDU', 'Education'), ('HSH', 'Human Science and Humanities'), ('CSE', 'Science and Engineering');
SELECT * FROM school;
3rd Command :
CREATE TABLE student(stuId INT(10), fname VARCHAR(20), lname VARCHAR(20), major VARCHAR(10), minor VARCHAR(10), credits INT(10), advisor INT);
INSERT INTO student (stuId, fname, lname, major, minor, credits, advisor) VALUES (100000,'Tony', 'Hawk', 'CSCI', 'CINF', 40, 1011), (100001, 'Mary', 'Hawk', 'CSCI', 'CINF', 35, 1011), (100002, 'David', 'Hawk', 'CSCI', 'ITEC', 66, 1012), (100003, 'Catherine', 'Lim', 'ITEC', 'CINF', 20, NULL), (100004, 'Larry', 'Johnson', 'ITEC' , NULL, 66, 1017), (100005, 'Linda', 'Johnson', 'CINF', 'ENGL', 13, 1015), (100006, 'Lilliam', 'Johnson', 'CINF', 'ITEC', 18,1016),(100007, 'Ben', 'Zico', NULL, NULL, 16, NULL),(100008, 'Bill', 'Ching', 'ARTS', NULL, 90, NULL),(100009, 'Linda', 'King', 'ARTS', 'CSCI', 125, 1018),(100111, 'Cathy', 'Johanson', NULL, NULL, 0, 1018);
select * from student;
4th Command :
CREATE TABLE class (classId INT(10), courseId INT(10), semester VARCHAR(10), year INT (5), FacId INT(10), room VARCHAR (10));
INSERT INTO class (classId, courseId, semester, year, facId, room) VALUES (10000, 2000, 'Fall', 2019, 1011,'D241'), (10001, 2001,'Fall', 2019, 1011, 'D242'),(10002, 2002, 'Fall', 2019, 1012, 'D136'),(10003, 2020, 'Fall', 2019, 1014, 'D241'),(10004,2021,'Fall', 2019, 1014, 'D241'),(10005, 2040, 'Fall', 2019, 1015, 'D237'), (10006, 2041, 'Fall', 2019, 1019, 'D217'), (10007, 2060, 'Fall', 2019, 1020, 'B101'),(10008, 2080, 'Fall', 2019, 1018, 'D241'),(11000, 2000, 'Spring', 2020, 1011, 'D241'),(11001,2001, 'Spring', 2020, 1012, 'D242'),(11002, 2002, 'Spring', 2020, 1013, 'D136'),(11003, 2020, 'Spring', 2020, 1016, 'D217'),(11004, 2061, 'Spring', 2020, 1018, 'B101');
select * from class
5th Command :
CREATE TABLE course (courseId INT(10), rubric VARCHAR(10), number INT(10), title VARCHAR(50), credits INT(5));
INSERT INTO course (courseId, rubric, number, title, credits) VALUES (2000, 'CSCI', 3333, 'Data Structure', 3),(2001, 'CSCI', 4333, 'Design of Database System', 3),(2002, 'CSCI', 5333, 'DBMS',3),(2020,'CINF',3321, 'Introduction to Information Systems',3),(2021, 'CINF', 4320, 'Web Application Development', 3),(2040, 'ITEC', 3335, 'Database Development', 3),(2041, 'ITEC', 3312, 'Introduction to Scripting', 3),(2060, 'ENGL', 1410, 'English I', 4),(2061, 'ENGL', 1311, 'English II', 3),(2080, 'ARTS', 3311, 'Hindu Arts',3),(2090, 'ACCT', 3333, 'Managerial Accounting', 3);
select * from course;
6th Command :
show tables ;
Step by step
Solved in 3 steps with 6 images
