Database System Concepts
7th Edition
ISBN: 9780078022159
Author: Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan
Publisher: McGraw-Hill Education
expand_more
expand_more
format_list_bulleted
Related questions
Question
thumb_up100%
Provide the MySQL commands for the following queries. Make sure that your
queries produce the exact output as shown (row order can be different).
[1] List the ids, first names, last names, majors, minors, and advisor's faculty ids of
students majoring or minoring in CSCI and having a faculty advisor in the following
manner.
+--------+-------+-------+-------+-------+---------+
| stuId | fname | lname | major | minor | advisor |
+--------+-------+-------+-------+-------+---------+
| 100000 | Tony | Hawk | CSCI | CINF | 1011 |
| 100001 | Mary | Hawk | CSCI | CINF | 1011 |
| 100002 | David | Hawk | CSCI | ITEC | 1012 |
| 100009 | Linda | King | ARTS | CSCI | 1018 |
+--------+-------+-------+-------+-------+---------+
4 rows in set (0.000 sec)
[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]>
| 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 | room |
1011 D241
1011
D242
1012
D136
1014
D241
1014
D241
1015
D237
1019 | D217
1928 | B191
1018
D241
2020
1011
D241
2020 |
1012
D242
2020 |
1013
D136
2020 |
1016 D217
2020 | 1918 | B191 |
-+
10000
10001
10002
10002
10003
10005
10004
10004
10005
10000
10006
10000
10007
10000
10008
10000
11000
11001
11002
11003
11004 |
2000 Fall
2001
Fall
2002 Fall
2020 Fall
2021 Fall
2040 Fall
2041 Fall
2060
Fall
show tables;
2080 Fall
2000 | Spring
2001 | Spring
2002 | Spring
2020 | Spring
2061 | Spring
2000 CSCI
2001 CSCI
2002 CSCI
2020 | CINF
2021 | CINF
2040 ITEC
2011
2041
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 corresponds to y
MariaDB [toyu2]> select * from course;
| courseId | rubric | number | title
3050
2060 ENGL
2061 | ENGL
2080 | ARTS
2090 ACCT
| ACCT
| ARTS
CINF
2019
2019
2019 |
2019 |
2010
2019 |
2019 |
2019
2019 |
2010
2019
3333 | Data Structures
4333
5333
2221
3321
5242
3312
1410
1311
3311
3333
Design of Database Systems
DBMS
4320
2235
3335 | Database Development
Introduction to Information Systems
Web Application Development
no
Introduction to Scripting
English I
English II
| Hindu Arts
| Managerial Accounting
11 rows in set (0.002 sec)
MariaDB [toyu2]> select * from department;
| deptCode | deptName
| schoolCode | numStaff
| Accounting
Arts
| BUS
HSH
| Computer Information Systems | CSE
10 |
5
5|
| credits |
3
3
3
3
3
3
3
4|
3
3
3 |](https://content.bartleby.com/qna-images/question/373ec5c3-fcc8-4cc5-a897-c792133242cb/76df5b9c-50eb-42c5-90bb-e2e1eae1c460/qv14d4c_processed.png)
Transcribed Image Text:MariaDB [toyu2]>
| 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 | room |
1011 D241
1011
D242
1012
D136
1014
D241
1014
D241
1015
D237
1019 | D217
1928 | B191
1018
D241
2020
1011
D241
2020 |
1012
D242
2020 |
1013
D136
2020 |
1016 D217
2020 | 1918 | B191 |
-+
10000
10001
10002
10002
10003
10005
10004
10004
10005
10000
10006
10000
10007
10000
10008
10000
11000
11001
11002
11003
11004 |
2000 Fall
2001
Fall
2002 Fall
2020 Fall
2021 Fall
2040 Fall
2041 Fall
2060
Fall
show tables;
2080 Fall
2000 | Spring
2001 | Spring
2002 | Spring
2020 | Spring
2061 | Spring
2000 CSCI
2001 CSCI
2002 CSCI
2020 | CINF
2021 | CINF
2040 ITEC
2011
2041
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 corresponds to y
MariaDB [toyu2]> select * from course;
| courseId | rubric | number | title
3050
2060 ENGL
2061 | ENGL
2080 | ARTS
2090 ACCT
| ACCT
| ARTS
CINF
2019
2019
2019 |
2019 |
2010
2019 |
2019 |
2019
2019 |
2010
2019
3333 | Data Structures
4333
5333
2221
3321
5242
3312
1410
1311
3311
3333
Design of Database Systems
DBMS
4320
2235
3335 | Database Development
Introduction to Information Systems
Web Application Development
no
Introduction to Scripting
English I
English II
| Hindu Arts
| Managerial Accounting
11 rows in set (0.002 sec)
MariaDB [toyu2]> select * from department;
| deptCode | deptName
| schoolCode | numStaff
| Accounting
Arts
| BUS
HSH
| Computer Information Systems | CSE
10 |
5
5|
| credits |
3
3
3
3
3
3
3
4|
3
3
3 |
![MariaDB [toyu2]>
| facId | fname
1011 Paul
1012
1013
1014
Sharon
1015 Daniel
1915
1916
1016 Andrew
1017 Dehors
1917
Deborah
1018
1918 Art
1019
A
A-
B
Mary
David
B+
B-
с
C+
D+
D-
F
| IP
P
| WX
1020
Benjamin Yu
Katrina
Bajaj
1021 Jorginlo Neymar
11 rows in set (0.001 sec)
MariaDB [toyu2]> select * from grade;
I grade | gradePoint |
|
select * from faculty;
| 1name | deptCode | rank
Smith
Tran
Love
Mannes
Kim
Byre
Gump
4.0000
3.6667
3.0000
3.3333
2.6667
2.0000
2.3333
1.6667
1.0000
1.3333
20
0.6667
20000
0.0000
NULL
NULL
NULL
CSCI
CSCI
CSCI
CSCI
CINF
CINE
CINF
ITEC
ITEC
Allister ARTS
ITEC
Business
Education
ENGL
ACCT
BUS
EDU
HSH
| CSE
4 rows in set (0.000 sec)
MariaDB [toyu2]> select * from student;
| stuId
fname
100000 Tony
100001 Mary
100002 David
Professor
Associate Professor
NULL
Assistant Professor
Professor
Associate Professor
Professor
Assistant Professor
15 rows in set (0.001 sec)
MariaDB [toyu2]> select from school;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB se
MariaDB [toyu2]> select * from school;
| schoolCode
schoolName
Lecturer
Lecturer
Assistant Professor
Human Sciences and Humanities
| Science and Engineering
1name | major | minor | credits
Hawk
CINF
Hawk
CINF
Hawk
100003 Catherine Lim
100004| Larry
100005 | Linda
CSCI
CSCI
CSCI
ITEC
ITEC CINF
Johnson ITEC NULL
Johnson CINF ENGL
40
35
66
20
66
13
advisor
1011
1011
1012
NULL
1017
1015](https://content.bartleby.com/qna-images/question/373ec5c3-fcc8-4cc5-a897-c792133242cb/76df5b9c-50eb-42c5-90bb-e2e1eae1c460/2i79a7o_processed.png)
Transcribed Image Text:MariaDB [toyu2]>
| facId | fname
1011 Paul
1012
1013
1014
Sharon
1015 Daniel
1915
1916
1016 Andrew
1017 Dehors
1917
Deborah
1018
1918 Art
1019
A
A-
B
Mary
David
B+
B-
с
C+
D+
D-
F
| IP
P
| WX
1020
Benjamin Yu
Katrina
Bajaj
1021 Jorginlo Neymar
11 rows in set (0.001 sec)
MariaDB [toyu2]> select * from grade;
I grade | gradePoint |
|
select * from faculty;
| 1name | deptCode | rank
Smith
Tran
Love
Mannes
Kim
Byre
Gump
4.0000
3.6667
3.0000
3.3333
2.6667
2.0000
2.3333
1.6667
1.0000
1.3333
20
0.6667
20000
0.0000
NULL
NULL
NULL
CSCI
CSCI
CSCI
CSCI
CINF
CINE
CINF
ITEC
ITEC
Allister ARTS
ITEC
Business
Education
ENGL
ACCT
BUS
EDU
HSH
| CSE
4 rows in set (0.000 sec)
MariaDB [toyu2]> select * from student;
| stuId
fname
100000 Tony
100001 Mary
100002 David
Professor
Associate Professor
NULL
Assistant Professor
Professor
Associate Professor
Professor
Assistant Professor
15 rows in set (0.001 sec)
MariaDB [toyu2]> select from school;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB se
MariaDB [toyu2]> select * from school;
| schoolCode
schoolName
Lecturer
Lecturer
Assistant Professor
Human Sciences and Humanities
| Science and Engineering
1name | major | minor | credits
Hawk
CINF
Hawk
CINF
Hawk
100003 Catherine Lim
100004| Larry
100005 | Linda
CSCI
CSCI
CSCI
ITEC
ITEC CINF
Johnson ITEC NULL
Johnson CINF ENGL
40
35
66
20
66
13
advisor
1011
1011
1012
NULL
1017
1015
Expert Solution
This question has been solved!
Explore an expertly crafted, step-by-step solution for a thorough understanding of key concepts.
Step by stepSolved in 2 steps with 2 images
Knowledge Booster
Learn more about
Need a deep-dive on the concept behind this application? Look no further. Learn more about this topic, computer-science and related others by exploring similar questions and additional content below.Similar questions
- The FOREIGN KEY constraint should be added to which table? ( refer to the tables in the JustLee Books database). a. the table representing the "one" side of a one-to-many relationship b. the parent table in a parent-child relationship c. the child table in a parent-child relationship d. the table that doesn’t have a primary keyarrow_forwardTask 6: The Car Maintenance team considered that the available maintenance tasks should also have the price information in the database. Alter the MAINTENANCE_TYPES table to include a new column named MAINTENANCE_PRICE of type FLOAT. ANSWER IN MYSQL PLEASEarrow_forwardAssignment 6: Creating the Babbage Bookkeeping Database Create a relationship between two tables. Specify referential integrity between the Bookkeeper table (the one table) and the Client table (the many table). Save the Relationships. Create the following queries. Create a query for the Client table. The query must display Client Number, Client Name, and Balance fields for all clients located in Empeer with a balance greater than $300.00. Do not display City field. Save the query as “Q21-Client-Empeer Query.” Create a query to display the Client Number, Client Name, and Address fields for all clients with an address on Maum. Save the query as “Q22-Client-Maum Query.” Create a query that will allow the user to enter the city to search when the query is run. The query results should display the Client Number, Client Name, and Bookkeeper Number. Test the query by searching for those records where the client is located in Portage. Save the query as “Q23-Client-City…arrow_forward
- Given the table PRODUCT(ProductID, ProductName, ProductClass, ProductWeight, ProductStyle, ProductColor, ProductPrice). Note: ProductWeight and ProductPrice are numeric fields, and the other attributes are character fields, i.e., store text. Write a SQL query to display the product class whose average price is at least 50arrow_forward8. The head of the hospital’s accounting department is concerned about the prescribed charge for services versus the actual charges for services in the surgery category of services (service_cat_id = ‘SUR’). Produce a listing of surgery services listed in the treatment table by service_id where the prescribed service_charge differs from the actual_charge recorded in the treatment table. The result table should also include the service_charge and actual-charge. Use a subquery approach to produce a listing of service_id values from the service table that belong to the ‘SUR’ category of services. Display the result table with the service and actual charges formatted to include the dollar sign ($)arrow_forwardFor this problem create a (temporary) table called instructor_course_nums. Write a procedure that accepts an instructor ID as input. The procedure calculates the total number of course sections taught by that instructor, and adds a tuple to the temporary table consisting of the instructors ID number, name, and total courses taught - call these attributes: ID, name, and tot_courses. If the instructor already has an entry in the table,then the procedure makes sure the total number of courses taught in the temporary table is up-to-date. You must name your procedure: Written in PostgreSQLarrow_forward
- You are working with a database table that contains invoice data. The table includes columns for invoice_id and billing_state. You want to remove duplicate entries for billing state and sort the results by invoice ID. You write the SQL query below. Add a DISTINCT clause that will remove duplicate entries from the billing_state column. NOTE: The three dots (...) indicate where to add the clause. SELECT ... FROM invoice ORDER BY invoice_id What billing state appears in row 17 of your query result? 1 point 1-CA 2-NV 3-WI 4-AZarrow_forwardPLZ help with the following: 1. Populate every relation with “sufficient” representative rows (at least 4 for each table). Create table book (title varchar(80) not null, year INT not null , num INT primary key , subject Varchar(50) null); Create table client (CID int primary key,fname varchar(20), lname varchar(20), type varchar(6), constraint checkclient check (type in ('Faculty', 'Student'))); Create table author (year INT, ID INT primary key, fname varchar(50) not null,lname varchar(50)); Create table BookAuthors(booknum int, authorID int, primary key (booknum, authorID), constraint fk_book foreign key (booknum) references book(num),constraint fk_author foreign key (authorid) references author(id)); create table bookcheckouts( booknum int, clientID int, checkoutdate date, expectedDuedate date not null, returnedate date, primary key (booknum, clientID, checkoutDate),constraint book_checks foreign key(booknum) references book(num),constraint book_clients foreign key…arrow_forwardCreate a view, using JOBS, DEPARTMENTS, and JOB_HISTORY tables that will include department name (not ID), job title (not jobID), start date, and end date for all rows in the JOB_HISTORY table Using your view, write a query that shows the number of rows (count) for each department. Write a query that uses your view from above to select the columns department name, job title, and end date for all rows where the department name includes the string “Sale” that is NOT case-sensitive. Use a function. i.e. SALE, SaLe, Sale, sAle, etc. should all match a rowarrow_forward
arrow_back_ios
arrow_forward_ios
Recommended textbooks for you
- Database System ConceptsComputer ScienceISBN:9780078022159Author:Abraham Silberschatz Professor, Henry F. Korth, S. SudarshanPublisher:McGraw-Hill Education
Starting Out with Python (4th Edition)Computer ScienceISBN:9780134444321Author:Tony GaddisPublisher:PEARSON
Digital Fundamentals (11th Edition)Computer ScienceISBN:9780132737968Author:Thomas L. FloydPublisher:PEARSON 
C How to Program (8th Edition)Computer ScienceISBN:9780133976892Author:Paul J. Deitel, Harvey DeitelPublisher:PEARSON
Database Systems: Design, Implementation, & Manag...Computer ScienceISBN:9781337627900Author:Carlos Coronel, Steven MorrisPublisher:Cengage Learning
Programmable Logic ControllersComputer ScienceISBN:9780073373843Author:Frank D. PetruzellaPublisher:McGraw-Hill Education
Database System Concepts
Computer Science
ISBN:9780078022159
Author:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan
Publisher:McGraw-Hill Education
Starting Out with Python (4th Edition)
Computer Science
ISBN:9780134444321
Author:Tony Gaddis
Publisher:PEARSON
Digital Fundamentals (11th Edition)
Computer Science
ISBN:9780132737968
Author:Thomas L. Floyd
Publisher:PEARSON
C How to Program (8th Edition)
Computer Science
ISBN:9780133976892
Author:Paul J. Deitel, Harvey Deitel
Publisher:PEARSON
Database Systems: Design, Implementation, & Manag...
Computer Science
ISBN:9781337627900
Author:Carlos Coronel, Steven Morris
Publisher:Cengage Learning
Programmable Logic Controllers
Computer Science
ISBN:9780073373843
Author:Frank D. Petruzella
Publisher:McGraw-Hill Education