CSE 3330-002 Chapter 6,7 Review Hyeonjun An

pdf

School

University of Texas, Arlington *

*We aren’t endorsed by this school

Course

3330

Subject

Computer Science

Date

Dec 6, 2023

Type

pdf

Pages

5

Uploaded by skypass359

Report
Hyeonjun An CSE 3330-002 Professor Guizani Feb. 3 rd , 2023 Chapter Review 1. Specify the following queries in SQL on the database schema of Figure 1.2. A. Retrieve the names of all senior students majoring in ‘cs’ (computer science). SELECT Student.Name FROM STUDENT WHERE (Student.class = 4 AND Student.Major = ‘CS’); B. Retrieve the names of all courses taught by Professor King in 2007 and 2008. SELECT Course.Course_name FROM COURSE, SECTION WHERE (Course.Course_number = Section.Course_number AND Section.Instructor = ‘King’AND (Section.Year = 07 OR Section.Year = 08)); C. For each section taught by Professor King, retrieve the course number, semester, year, and number of students who took the section. SELECT Section.Course_number, Section.Semester, Section.Year, count(*) FROM Section, Grade_report WHERE (Section.Section_identifier = Grade_report.Section_identifier AND Section.Instructor = ‘King’); D. Retrieve the name and transcript of each senior student (Class = 4) majoring in CS. A transcript includes course name, course number, credit hours, semester, year, and grade for each course completed by the student. SELECT Student.Name, Course.Course_name, Course.Course_number, Course.Credit_hours, Section.Semester, Section.Year, Grade_report.Grade FROM Student, Course, Section, Grade_report WHERE (Course.Course_number = Section.Course_number AND Section.Section_identifier = Grade_report.Section_identifier AND Student.Student_number = Grade_report.Student_number AND Student.Class = 4 AND Student.Major = ‘CS’); E. Retrieve the names and major departments of all straight-A students (students who have a grade of A in all their courses). SELECT Student.Name, Student.Major FROM Student WHERE NOT EXISTS( SELECT * FROM Grade_report WHERE Student.Student_number = Grade_report.Student_number AND NOT(Grade_report.Grade = ‘A’));
F. Retrieve the names and major departments of all students who do not have a grade of A in any of their courses. SELECT Student.Name, Student.Major FROM Student WHERE NOT EXISTS( SELECT * FROM Grade_report WHERE Student.Student_number = Grade_report.Student_number AND Grade_report.Grade = ‘A’); 2. Specify the following queries on the database in Figure 5.5 in SQL. A. For each department whose average employee salary is more than $30,000, retrieve the department name and the number of employees working for that department. SELECT Dname, count (*) FROM Department, Employee WHERE Dnumber=Dno GROUP BY Dname HAVING avg (Salary) > 30000; B. Suppose that we want the number of male employees in each department making more than $30,000, rather than all employees. Can we specify this query in SQL? Why or why not? SELECT Dname, count(*) FROM Department, Employee WHERE Dnumber = Dno AND Sex = ‘Male’ AND Dno IN ( SELECT Dno FROM Employee GROUP BY Dno HAVING Salary > 30000) GROUP BY Dname; C. Retrieve the names of all employees who work in the department that has the employee with the highest salary among all employees SELECT Fname, Lname FROM Employee WHERE Dno = ( SELECT Dno FROM Employee WHERE Salary = ( SELECT max(Salary) FROM Employee)); D. Retrieve the names of all employees whose supervisor's supervisor has '888665555' for Ssn. SELECT Fname, Lname
FROM Employee WHERE Super_ssn IN ( SELECT SSN FROM Employee WHERE Super_ssn = ‘888665555’); E. Retrieve the names of employees who make at least $10,000 more than the employee who is paid the least in the company. SELECT Fname, Lname FROM Employee WHERE Salary >= 10000 + ( SELECT MIN(Salary) FROM Employee); F. Find the average salary for employees in each department. SELECT Dname, avg(Salary) FROM Employee, Department WHERE Dname = Dno GROUP BY Dname; 3. Specify the following queries in SQL on the database schema of Figure 6.6. A. Retrieve the most popular books in the library SELECT Book_id FROM Book_loans GROUP BY Book_id HAVING count(*) = ( SELECT max(cnt) FROM ( SELECT count(*) as cnt FROM Book_loans GROUP BY Book_id) tmp); B. List branch addresses that house the book titled ‘Don Quixote’ SELECT Branch_name, Address FROM ((Book NATUAL JOIN Book_copies) NATURAL JOIN Library_branch) WHERE Title = ‘Don Quixote’; C. Find all borrowers who have checked books authored by: 'JK Rowling' SELECT Name FROM (((Book NATURAL JOIN Book_Authors) NATURAL JOIN Book_loans) NATURAL JOIN BORROWER) WHERE Author_name = ‘JK Rowling’; D. Retrieve the number of books checked out by a particular borrower: 'Hughie Prim' SELECT count(*) FROM Book_loans, Borrower WHERE Book_loans.Card_no = Borrower.Card_no AND Name = ‘Hughie Prim’;
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
E. Retrieve the total number of checkouts for each borrower. SELECT Name, count(*) FROM Book_loans, Borrower WHERE Book_loans.Card_no = Borrower.Card_no GROUP BY Name; F. Find the book that had the minimum number of checkouts. SELECT Book_id FROM Book_loans GROUP BY Book_id HAVING count(*) = ( SELECT min(cnt) FROM ( SELECT count(*) as cnt FROM Book_loans GROUP BY Book_id) tmp); 4. Consider the following view, DEPT_SUMMARY, defined on the COMPANY database in Figure 5.6: CREATE VIEW DEPT_SUMMARY ( D, C, TOTAL_S, AVERAGE_S) AS SELECT DNO, COUNT(*), SUM(SALARY), AVG(SALARY) FROM EMPLOYEE GROUP BY DNO; State which of the following queries and updates would be allowed on the view. If a query or update would be allowed, show what the corresponding query or update on the base relations would look like, and give its result when applied to the database in Figure 5.6. SELECT * FROM DEPT_SUMMARY; > allowed, SELECT Dno, count(*), sum(Salary), avg(Salary) FROM Employee GROUP BY Dno; SELECT D, C FROM DEPT_SUMMARY WHERE TOTAL_S > 100000; > allowed, SELECT Dno, count(*) FROM Employee GROUP BY Dno HAVING sum(Salary) > 100000; SELECT D, AVG_S FROM DEPT_SUMMARY WHERE C > (SELECT C FROM DEPT_SUMMARY WHERE D = 4); > allowed, SELECT Dno, avg(Salary)
FROM Employee GROUP BY Dno HAVING count(*) > ( SELECT count(*) FROM Employee WHERE Dno = 4); UPDATE DEPT_SUMMARY SET D=3 WHERE D= 4; > This would update all employees from the department 4 to the department 3. DELETE FROM DEPT_SUMMARY WHERE C > 4; >This would delete all departments with more than 4 employees from the database.

Related Documents

lab 5.docx
lab 11.docx
Lab 2.docx
Siddique, In Class Assignment 1 Revised.docx
DAD 220 Database Documentation 6-1 Project 1 Golda Smith.docx
Assignment # 4 Analyzing Classroom Data to Plan Intervention.docx
CSE 3330-002 Chapter 8 Review Hyeonjun An.pdf
Lab_Assignment_2 - 2.html
Chapter 2 Homework-1.docx
Midterm_Exam - Solutions.pdf
Homework_Assignment_1 - Solutions.pdf
Homework_Assignment_1 - 5.75.docx
Recommended textbooks for you
Text book image
A Guide to SQL
Computer Science
ISBN:9781111527273
Author:Philip J. Pratt
Publisher:Course Technology Ptr
Text book image
Oracle 12c: SQL
Computer Science
ISBN:9781305251038
Author:Joan Casteel
Publisher:Cengage Learning
Text book image
Database Systems: Design, Implementation, & Manag...
Computer Science
ISBN:9781285196145
Author:Steven, Steven Morris, Carlos Coronel, Carlos, Coronel, Carlos; Morris, Carlos Coronel and Steven Morris, Carlos Coronel; Steven Morris, Steven Morris; Carlos Coronel
Publisher:Cengage Learning
Text book image
Database Systems: Design, Implementation, & Manag...
Computer Science
ISBN:9781305627482
Author:Carlos Coronel, Steven Morris
Publisher:Cengage Learning
SEE MORE TEXTBOOKS
Recommended textbooks for you
Text book image
A Guide to SQL
Computer Science
ISBN:9781111527273
Author:Philip J. Pratt
Publisher:Course Technology Ptr
Text book image
Oracle 12c: SQL
Computer Science
ISBN:9781305251038
Author:Joan Casteel
Publisher:Cengage Learning
Text book image
Database Systems: Design, Implementation, & Manag...
Computer Science
ISBN:9781285196145
Author:Steven, Steven Morris, Carlos Coronel, Carlos, Coronel, Carlos; Morris, Carlos Coronel and Steven Morris, Carlos Coronel; Steven Morris, Steven Morris; Carlos Coronel
Publisher:Cengage Learning
Text book image
Database Systems: Design, Implementation, & Manag...
Computer Science
ISBN:9781305627482
Author:Carlos Coronel, Steven Morris
Publisher:Cengage Learning
SEE MORE TEXTBOOKS