Registration- PK,FK1 studentID int NOT NULLPK,FK2 sectionID int NOT NULLHPKStudentsregistrationDate date NOT NULLfinalClassGradestudentID int NOT NULLfirstName varChar2 (25) NOT NULLlastName varChar2 (50) NOT NULLadmissionDate date NOT NULLPK assignmentTypeID int NOT NULLdescription varChar2(255) NOT NULLAssignmentScorePK,FK1 studentID int NOT NULLPK,FK2sectionID int NOT NULLtuitionBalancePK, FK3 assignmentTypeID int NOT NULLassignment Number int NOT NULLscore number(3,0)AssignmentCodePKstreetAddress varChar2(255) NOT NULLcity varChar2(255) NOT NULLstate varChar2(2) NOT NULLzip varChar2(9) NOT NULLPhone varChar2(10) NOT NULLCoursesro PK CourselD int NOT NULLAK1 subjectCodeAK1courseNumbersubject Descriptioncourse Description varChar2(255) NOT NULLHFK prerequisite intLocationPK locationID int NOT NULLBuilding Varchar2(5)Room Varchar2(5)SectionsHPK sectionID int NOT NULLFK1courselD int NOT NULLsection StartDate date NOT NULLFK2 locationID intFK3 professorID intcapacity intProfessorPK professorID int NOT NULLfirstName varChar2 (25) NOT NULLlastName varChar2 (50) NOT NULLstreetAddress varChar2(255) NOT NULcity varChar2(255) NOT NULLstate varChar2(2) NOT NULLzip varChar2(9) NOT NULLphone varChar2(10) NOT NULLhireDate date NOT NULL 1. Provide the professor ID, first and last name of the professor who teach WEB courses that have thehighest capacity of all sections. Show the section ID, subject code and course number. Arrange thelist by course number.PROFESSORID FIRSTNAMESECTIONID SUBJ COURS10083 Ellis20193031760 WEB 140010616 Gisele20201011445 WEB 170010125 Lisandra20201011459 WEB 250010000 Lon20203031998 WEB 262010083 Ellis20202020751 WEB 320010255 David20193030841 WEB 435010255 David20202020795 WEB 48907 rows selected.LASTNAMELansangOverdickRodgersonAddoLansangBushardBushard

A database is an organized collection of data, typically stored electronically in a computer system.…

1. Provide the professor ID, first and last name of the professor who teach WEB courses that have the highest capacity of all sections. Show the section ID, subject code and course number. Arrange the list by course number. PROFESSORID FIRSTNAME SECTIONID SUBJ COURS 10083 Ellis 20193031760 WEB 1400 10616 Gisele 20201011445 WEB 1700 10125 Lisandra 20201011459 WEB 2500 10000 Lon 20203031998 WEB 2620 10083 Ellis 20202020751 WEB 3200 10255 David 20193030841 WEB 4350 10255 David 20202020795 WEB 4890 7 rows selected. LASTNAME Lansang Overdick Rodgerson Addo Lansang Bushard Bushard

1. Provide the professor ID, first and last name of the professor who teach WEB courses that have the highest capacity of all sections. Show the section ID, subject code and course number. Arrange the list by course number. PROFESSORID FIRSTNAME SECTIONID SUBJ COURS 10083 Ellis 20193031760 WEB 1400 10616 Gisele 20201011445 WEB 1700 10125 Lisandra 20201011459 WEB 2500 10000 Lon 20203031998 WEB 2620 10083 Ellis 20202020751 WEB 3200 10255 David 20193030841 WEB 4350 10255 David 20202020795 WEB 4890 7 rows selected. LASTNAME Lansang Overdick Rodgerson Addo Lansang Bushard Bushard

Database System Concepts

7th Edition

ISBN:9780078022159

Author:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan

Publisher:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan

Chapter1: Introduction

Section: Chapter Questions

Problem 1PE

See similar textbooks

Similar questions

Normalise the table to 3rd Normal Form.
Create the table that would result from Tstu_code (student). FIGURE Q3.8 THE CH03_COLLEGEQUE DATABASE TABLES Database name: Ch03_CollegeQue Table name: STUDENT STU CODE PROF_CODE 100278 128569 512272 531235 531268 2 553427 1 Table name: PROFESSOR PROF CODE DEPT CODE 12 26 36 44
Task 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 PLEASE
Question 2 and 3
4. In the OrderItems table, display a count of the number of products, grouped by prod_id, but only for those groups having 3 or more of a given prod_id. Display the count with the alias shown. Sort by prod_id in ascending order. BNBG01 BNBG02 BNBG03 BR03 4 rows returned in 0.00 seconds PROD_ID Download 3 3 3 4 NUM_PROD_ID
Data normalisation
The query in the figure below. PROGRAM 1 Dept ID PROGRAM ACADEMIC ADVISOR STUDENT OFFICE PHONE 1 SID Name GPA Gender Dept ID Field: SID Table: STUDENT Sort Show Criteria: Name STUDENT PROGRAM PROGRAM GPA STUDENT Accounting" or Name and PROGRAM for the students who registered in the A program "Accounting" will be listed SID, Name, PROGRAM, and GPA for the students who registered in the B program "Accounting" will be listed SID and GPA for the students who registered in the program "Accounting" will be listed Name and GPA for the students who registered in the program "Accounting will be listed
Display the country names and region names where no warehouse is located in them. Use set operators to create this report and provide the exact sorting as below.
Question 3 refers to two data tables giving the details of a company's employees and company's job titles, called EMP and JOB respectively, which have the following format: Table EMP Column name Column type Integer Up to 10 characters Example entry Description Employee ID number Employee surname EMPNO 7369 ΕΝΑΜΕ SMITH Integer Integer Employee's job title No ID no. of employee's JOBNO 10 MGR 7566 manager Date employment started Monthly salary HIREDATE Date 17/12/1980 SAL Integer 1600 Table JOB Column type Integer Up to 15 characters Column name Example entry Description job title No Employee's job title JOBNO 10 JOB TITLE SALESMAN b) Using EMP and JOB tables, give an example of the JOIN SQL statement which uses "WHERE", "GROUP BY", “HAVING" and “ORDER BY" clauses. Explain the result table of the SQL statement. (Maximum 300 words).
Find the Primary Keys and P3,6 for the database: Make Model Engine ID Ford Mustang Ford Fiesta Chevy Camaro Chevy Caprice Olds Cutlass Olds Cutlass Volvo P1800 Volvo 240 Volvo 760 Year 1972 A1222 1989 C54322 1991 754342AH 1989 442355CC 1992 ANDU33 1992 ANGH28 1969 44325XX 1986 53526PD 1992 578868R Vehicle ID FO13579 FO24245 CH172389 CH156738 OL64332 OL61998 V044526 VO64690 V083529 Color Black Yellow Black Yellow Blue White White Black Blue
Based on the ERD below, display the first and last names, hire dates, and number of years employed for all employees who have worked for the organization more than 15 years. Order by the most recent hire date.