Design a new database with a minimum of 6 tables. Include appropriate constraints to create the tables. ● Your database MUST CONTAIN at least 6 (or more) TABLES! ● . . . An ERD or updated ERD. At least two tables should use a sequence to generate primary key values. Produce a database script file (.sql) that will create the database. Produce a script file to add data to the database. Produce a script file with 15 queries: At least four using join • At least three using where clause - At least three using order by At least two using single row functions • At least two using group by and group functions at least one nested query Atleast 4 queries from chapter 1 - 6 (exclude Create Table, insert queries)

Design a new database with a minimum of 6 tables. Include appropriate constraints to create the tables. ● Your database MUST CONTAIN at least 6 (or more) TABLES! ● . . . An ERD or updated ERD. At least two tables should use a sequence to generate primary key values. Produce a database script file (.sql) that will create the database. Produce a script file to add data to the database. Produce a script file with 15 queries: At least four using join • At least three using where clause - At least three using order by At least two using single row functions • At least two using group by and group functions at least one nested query Atleast 4 queries from chapter 1 - 6 (exclude Create Table, insert queries)

A Guide to SQL

9th Edition

ISBN:9781111527273

Author:Philip J. Pratt

Publisher:Philip J. Pratt

Chapter2: Database Design Fundamentals

Section: Chapter Questions

Problem 5TD

See similar textbooks

Similar questions

Which of the following data dictionary objects should be used to view information about the constraints in a database? USER_TABLES USER_RULES USER_COLUMNS USER_CONSTRAINTS None of the above objects should be used.
Assume that the Authors table is already built in the database with the following specifications: Authors: Column Name Data Type constrains Authors ID Author Name Varchar2(10) |Varchar2(30) Primary key Unique You Need to Create the Books table with the following specifications Column Name Data Type Constrains ISBN Book Title Book Price Author id Number (10) Varchar2(40) Number (4,2) Varchar2(10) Primary key Not Null referencing the Author_ID in the Author Table Complete the create statement by drag and drop the correct word: Create Table lblank (ISBN Number (10) plank, Book Title Varchar2blank ) Not Null, Book Price Number(4, 2), Auther IR blank (10) references Jolank (blank ); Author ID Books Number||Authors Not Nul Varchar2| Primary Key 40 h (United States)
Write Mysql code for: The database has three tables for tracking horse-riding lessons: Horse with columns: ID - primary key RegisteredName Breed Height BirthDate Student with columns: ID - primary key FirstName LastName Street City State Zip Phone EmailAddress LessonSchedule with columns: HorseID - partial primary key, foreign key references Horse(ID) StudentID - foreign key references Student(ID) LessonDateTime - partial primary key Write a SELECT statement to create a lesson schedule for Feb 1, 2020 with the lesson date/time, student's first and last names, and the horse's registered name. Order the results in ascending order by lesson date/time, then by the horse's registered name. Make sure unassigned lesson times (student ID is NULL) appear in the results. Hint: Perform a join on the LessonSchedule, Student, and Horse tables, matching the student IDs and horse IDs.
Access Assignment Problem: JMS TechWizards is a local company that provides technical services to several small businesses in the area. The company currently keeps its technicians and clients’ records on papers. The manager requests you to create a database to store the technician and clients’ information. The following table contains the clients’ information. Client Number Client Name Street City State Postal Code Telephone Number Billed Paid Technician Number AM53 Ashton-Mills 216 Rivard Anderson TX 78077 512-555-4070 $315.50 $255.00 22 AR76 The Artshop 722 Fisher Liberty Corner TX 78080 254-555-0200 $535.00 $565.00 23 BE29 Bert's Supply 5752 Maumee Liberty Corner TX 78080 254-555-2024 $229.50 $0.00 23 DE76 D & E Grocery 464 Linnell Anderson TX 78077 512-555-6050 $485.70…
Note: Use MYSQL Create a database named book-collection-db and table named: bookcollect. Your tableshould contain the following columns: Title, Author, Genre (Children’s, Romance,Thriller, Fantasy, etc.), Publisher, Copyright Date, ISBN Number, Category (Fiction/Nonfiction), Overview, Link Where to Buy, and Price. Create 30 rows containing list of bookwith varied genre.
assume that a database has a table named Stock, with thefollowing columns:Column Name TypeTrading_Symbol nchar(10)Company_Name nchar(25)Num_Shares intPurchase_Price moneySelling_Price money Write a Select statement that returns the Trading_Symbol column only from the rows where Selling_Price is greater than Purchase_Price and Num_Shares is greater than 100.
adultsData.csv file: https://onq.queensu.ca/d2l/common/viewFile.d2lfile/Database/MzA1MzUwMzA/adultsData.csv?ou=748849
true/false INNER JOIN combines two relations. It takes the second relation, and appends it to the end of the first relation. In Corelated Subqueries, subquery is executed independent of the outer query: subquery is executed only once. SELECT * FROM sys.Relations displays information about all the user-defined relations within a database. Given GENDER and STUDENT relations, SELECT Gender, FName, LName FROM GENDER LEFT OUTER JOIN STUDENT ON STUDENT.GenderCode = GENDER.GenderCode ≡ SELECT Gender,FName, LName FROM STUDENT RIGHT OUTER JOIN GENDER ON STUDENT.GenderCode = GENDER.GenderCode The statements select RIGHT('I wish I had attended the practice hours', 9) and select…
Using the WideWorldDistributors database and the Sales schema create a function that takes InvoiceID as an input parameter and returns all the rows from the table InvoiceLines for the InvoiceID value passed into the function. If the result is a NULL return a zero
You are required to create the following tables in a database named SICKLEAVE:EMPLOYEESEMPLOYEE_ID VARCHAR(5) NOT NULL PRIMARY KEYEMPLOYEE_NAME VARCHAR(30) NOT NULLEMPLOYEE_SURNAME VARCHAR(30) NOT NULLDATE_OF_BIRTH DATE NOT NULL DOCTORSDOCTOR_ID VARCHAR(5) NOT NULL PRIMARY KEYDOCTOR_NAME VARCHAR(30) NOT NULL EMPLOYEE_SICKLEAVEEMPLOYEE_ID VARCHAR(5) NOT NULL PRIMARY KEYFOREIGN KEY REFERENCESEMPLOYEES(EMPLOYEE_ID)START_DATE DATE NOT NULL PRIMARY KEYDOCTOR_ID VARCHAR(5) NOT NULLNUMBER_OF_DAYS SMALLINT NOT NULL
You are required to create the following tables in a database named STUDENT_REGISTRATIONS. Ensure that you create the database and table objects exactly as depicted below.STUDENTSSTUDENT_ID VARCHAR(8) NOT NULL PRIMARY KEYSTUDENT_NAME VARCHAR(40) NOT NULLSTUDENT_SURNAME VARCHAR(40) NOT NULLMODULESMODULE_ID VARCHAR(8) NOT NULL PRIMARY KEYMODULE_NAME VARCHAR(40) NOT NULLMODULE_CREDIT SMALLINT NOT NULLSTUDENT_MODULESSTUDENT_ID VARCHAR(8) NOT NULL PRIMARY KEYFOREIGN KEY REFERENCES STUDENTS(STUDENT_ID)MODULE_ID VARCHAR(8) NOT NULL PRIMARY KEYFOREIGN KEY REFERENCES MODULES(MODULE_ID)LECTURERSLECTURER_ID VARCHAR(8) NOT NULL PRIMARY KEYLECTURER_NAME VARCHAR(40) NOT NULLLECTURER_SURNAME VARCHAR(40) NOT NULL LECTURER_MODULESMODULE_ID VARCHAR(8) NOT NULL PRIMARY KEYFOREIGN KEY REFERENCES MODULES(MODULE_ID)LECTURER_ID VARCHAR(8) NOT NULL PRIMARY KEYFOREIGN KEY REFERENCES
You are required to create the following tables in a database named SICKLEAVE:EMPLOYEESEMPLOYEE_ID VARCHAR(5) NOT NULL PRIMARY KEYEMPLOYEE_NAME VARCHAR(30) NOT NULLEMPLOYEE_SURNAME VARCHAR(30) NOT NULLDATE_OF_BIRTH DATE NOT NULLDOCTORSDOCTOR_ID VARCHAR(5) NOT NULL PRIMARY KEYDOCTOR_NAME VARCHAR(30) NOT NULLEMPLOYEE_SICKLEAVEEMPLOYEE_ID VARCHAR(5) NOT NULL PRIMARY KEYFOREIGN KEY REFERENCESEMPLOYEES(EMPLOYEE_ID)START_DATE DATE NOT NULL PRIMARY KEYDOCTOR_ID VARCHAR(5) NOT NULLNUMBER_OF_DAYS SMALLINT NOT NULL(20)Q.1.2 Populate the tables created in Q.1.1 with the following data:EMPLOYEESEMPLOYEE_ ID EMPLOYEE_NAME EMPLOYEE_SURNAMEDATE_OF_BIRTHI0001 Dominique Woolridge 1993-04-19I0002 Nico Baird 1991-11-19I0003 Derek Moore 1992-06-24I0004 Neo Petlele 1993-12-29I0005 Andrew Crouch 1994-01-30DOCTORS(10)Question 2 (Marks: 20)Q.2.1 Write a query that will display doctors' names that have not issued any doctor’scertificates to employees yet.Sample Results:DOCTOR_NAMEJulia Robbins(5)DOCTOR_ID…