YSQL [jsohn6]> SELECT * FROM SCHOOL; lySQL [jsohn6]> SELECT * FROM DEPARTMENT; SCHOOL_CODE DEPARTMENT_ID | DEPARTMENT_ID | DEPARTMENT_NAME 101 SCHOOL OF MERCHANDISING FASHION DESIGN 101 SCHOOL OF FASHION DESIGN 102 ADVERTISING 102 103| 201|| 202 | SCHOOL OF GRAPHIC DESIGN GRAPHIC DESIGN 103 SCHOOL OF FINE ARTS 201 FINE ARTS SCHOOL OF DESIGN AND TECHNOLOG DESIGN AND TECHNOLOGY 202 rows in set (0.00 sec) MYSQL [jsohn6]> SELECT FROM PROFESSOR; MYSQL [jsohn6]> SELECT * FROM TECHNOLOGY; | PROF_NUM | PROF_LASTNAME PROF_FIRSTNAME COURSE_TYPE | LABTOP_NUMBER 1 | Smith 2 | Banks 3 | Taylor 4 | Smith 6 | Smith 7 | Banks Mary | Jane TECH-100 12345 Hazel TECH-110 67890 Terry TECH-120 13579 | Jenny | Tim 24680 TECH-200 4 rows in set (0.00 sec) 6 rows in set (0.00 sec) MYSQL [jsohn6]> SELECT * FROM COURSE; | COURSE_TYPE | PROF_NUM | DEPARTMENT_ID | COURSE_TITLE | COURSE_DESCRIPTION | COURSE_TYPE_CODE 101 | Costume Designs 101 | Couture Fashion 101 | Costume Designs 101 | Textile Designs 201 | Fine Arts 103 | Computer Patterns 103 Web Retail 103 | Web Design 202 | Design with Technolo | Intro to Computer Design | Fundamentals of Costume Design F | Fundamentals of Luxury Design | F | Fundamentals of Costume Design |F | Fundamentals of Textile Design | F | Advanced Printmaking | Intro to Computer Patterns | Intro to Retail Ecommerce | Fundamentals of Graphic Design | T FASH-100 FASH-110 FASH-120 7| FASH-150 FASH-210 4 | TECH-100 TECH-110 2 4 | 2 | TECH-120 TECH-200 9 rows in set (0.01 sec)

Write the SQL code required to list the employee number, last name, first name, and middle initial of all employees whose last names start with Smith. In other words, the rows for both Smith and Smithfield should be included in the listing. Sort the results by employee number. Assume case sensitivity.  In the SQL Query do the following  Select clause: Emp_Num, Emp_LName, Emp_FName, Emp_Initial  From: EMPLOYEE   Where clause: last name starts with Smith. (You will be using Like operator) Order by clause: Emp_Num

ERD:- PK CHECKOUT Check Num FK1 Book_Num FK2 Pat_ID Check Out Date Check_Due Date Check_In_Date PK BOOK Book Num Book Title Book_Year Book_Cost Book_Subject FK1 Pat_ID -H- PATRON PK Pat ID Pat_FName Pat_LName Pat_Type WRITES PK,FK1 Book Num PK,FK2 Au ID AUTHOR PK AU ID Au_FName Au_LName Au_BirthYear

Problem 63 Write a query to display the checkout number, book number, patron ID, checkout date, and due date for every checkout that has ever occurred in the system. Sort the results by checkout date in descending order and then by checkout number in ascending order (Partial results are shown in Figure P7.63).

Problem 90 Write a query to display the author last name, author first name, and book number for each book written by that author. Sort the results by author last name, first name, and then book number (Figure P7.90). (25 rows)

Problem 9 Write a query to count the number of invoices.

3. Run a query that gives the average grade on the final exam for male students and for femalestudents. Round that number to 1 decimal place only.

The given SQL creates a Song table and inserts some songs. The first SELECT statement selects songs released after 1992. The second SELECT statement selects the release year for song with ID 800. Create a third query that combines the two existing queries. The first SELECT should be the outer query, and the second SELECT should be the subquery. The ORDER BY clause should appear after the subquery. Run your solution and verify the new query returns a result table with five rows, all with release years after 1992. CREATE TABLE Song (ID INT,Title VARCHAR(60),Artist VARCHAR(60),ReleaseYear INT,Genre VARCHAR(20),PRIMARY KEY (ID)); INSERT INTO Song VALUES(100, 'Hey Jude', 'Beatles', 1968, 'pop rock'),(200, 'You Belong With Me', 'Taylor Swift', 2008, 'country pop'),(300, 'You\'re Still the One', 'Shania Twain', 1998, 'country pop'),(400, 'Need You Now', 'Lady Antebellum', 2011, 'country pop'),(500, 'You\'ve Lost That Lovin\' Feeling', 'The Righteous Brothers', 1964, 'R&B'),(600, 'That\'s…

Given a small company database tables below. Create the database and tables for the company and answer all the questions below. LINE CUSTOMER VENDOR INV_NUMBER LINE_NUMBER P_CODE LINE_UNITS LINE_PRICE Cus CODE CUS_LNAME CUS_FNAME CUS_INITIAL cus AREACODE CUS_PHONE CUS_BALANCE V_CODE V_NAME V_CONTACT V_AREACODE V_PHONE V_STATE V_ORDER 1000 3 23100-HB 0.05 10010 Ramas Afred 015 844-2573 21225 Bryson, Ine. Smithson 015 223-3234 TN 1006 4 89-WRE-Q 256.00 10011 Dunne Leona 713 804-1238 21220 SuperLoo, Inc. Flushing 04 215-8006 FL N 1 13-Q2/P2 2 14.00 1007 10012 Smith Kathy 015 804-2205 345.88 21231 DSE Supply Singh 815 228-3246 TN 1007 254778-2T 4.00 10013 Olowski Paul 815 804-2100 530.75 21344 Gomez Bros. Ortega 815 889-2546 KY N 1008 1 PVC23DRT 5.87 10014 Orlando Myron le15 222-1872 22507 Dome Supply Smith 01 e78-1419 GA IN 1008 2 WR3/TT3 3 110.05 10015 OBrian Amy 713 442-3381 23110 Randsets Ltd. Anderson 001 078-3008 GA 1008 3 23100-HB 0.05 10010 Brown James 015 297-1228 221.19 24004…

Problem 98 Write a query to display the book number, title, and number of times each book has been checked out. Include books that have never been checked out. Sort the results in descending order by the number of times checked out and then by title (Figure P7.98).