MySQL Database Project Section 1- SQL Basics Part 1: Database and Tables Create
ID: 3772443 • Letter: M
Question
MySQL Database Project
Section 1- SQL Basics
Part 1: Database and Tables
Create a database with the following requirements:
_Minimum of 3 tables
_Minimum of 15 fields (distributed between the 3 tables)
_The tables must have relationships established (PK to FK)
Part 1 Receivables:
1. Write a script that creates the database and tables using the following parameters:
a. The primary keys for the tables must be auto-generated number
b. Correct data types must be assigned to the fields
c. Correct references must be used so that the tables are related.
Save the script as SP1.
Part 2: Fill the Tables with Records
Create a script that adds a minimum of 50 combined records into the 3 tables:
Part 2 Receivables:
3. Write a single script that inserts a minimum of 50 records into the 3 tables. Save the script as SP3.
Part 3: Create Views
Write scripts that create views (give each view a meaningful name) based on your database tables with the following requirements:
Part 3 Receivables:
4. An updateable view (that can be used for an insert into script) based on the table of your choice. Save the script as SP4.
a. Create an SQL script that inserts a record using the view listed above. Save the script as SP4a.
5. A view that uses all 3 tables and has 2 WHERE conditions. Save the script as SP5.
6. A summary view that uses two tables with 2 aggregate columns. Save the script as SP6.
Section 2- Advanced SQL
Part 4:
7. Stored Procedure:
a. Create a stored procedure that has 2 input variables and 1 return value. The input variables should be used as filter conditions. Name it accordingly using the correct prefix and what it does (abbreviated- such as xxTopInv). Save the script as SP7a.
b. Create a script that calls the stored procedure using the input variables. Save the script as SP7b.
8. Stored Procedure:
a. Create a stored procedure that has 1 input variables and 1 return value. The input variables should be used as filter conditions. Include and IF-ELSE conditional within the procedure. Name it accordingly using the correct prefix and what it does (abbreviated- such as xxTopInv). Save the script as SP8a.
b. Create a script that calls the stored procedure using the input variables. Save the script as SP8b.
9. Stored Procedure:
Create a stored procedure that performs an update on one field in one of your tables using a WHILE-DO loop. Save the script as SP9a.
b. Create a script that selects everything from the table first, calls the procedure and then selects everything from the table once again (before and after look at the table results). Save the script as SP9b.
10. Dynamic SQL:
a. Create a stored procedure that uses dynamic sql and has 1 input parameter and returns 6 rows (OR FIELDS) from 2 tables. Save the script as SP10.
b. Create a script that calls the dynamic sql stored procedure using the input variable. Save the script as SP10b.
11. Stored Function:
a. Create a stored function that uses 2 input parameters. You may use as many tables as you would like. Save the script as SP11
b. Create a script that calls the stored function using the 2 input variables. Save the script as SP11b.
Explanation / Answer
Part-1:
Using MySQL 5.6:
CREATE TABLE Student
(
StudentID int NOT NULL AUTO_INCREMENT,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
Course varchar(4),
dateOfJoining DATE,
PRIMARY KEY (StudentID)
)
CREATE TABLE YearofStudent
(
PassOutYear int NOT NULL,
Grade varchar(4),
Remarks varchar(20),
PRIMARY KEY (PassOutYear),
CONSTRAINT fk_PerOrders FOREIGN KEY(StudentID)
REFERENCES Student(StudentID)
)
CREATE TABLE Exam
(
ExamID int NOT NULL,
ExamName varchar(20),
PRIMARK KEY (ExamID),
CONSTRAINT fk_PerOrders FOREIGN KEY(StudentID)
REFERENCES Student(StudentID)
CONSTRAINT fk_PerOrders FOREIGN KEY(PassOutYear)
REFERENCES YearofStudent(PassOutYear)
)
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.