We Alsoofferteam Training Classes in Kata, Kumite and Kobudo Forour Competition Team

Introduction to Databases

CST 1204

Final Exam

[STUDENT NAME]

[STUDENT EMPID]

Prof. Gonzalez

Dec 15, 2017

1- [database 50%]

Given a business situation, in which managers require information from a database, determine, analyze and classify the information so that Queries can be created to speculate trends in the business.Based on the information provided below, create a database for Kai Leung's Shotojuku. Shihan Kai Leung has an intensive background in the Martial Arts and has been offering instructions since the 1970's. The Shotokan style emphasize rigorous physical training to develop the clean powerful linear and focus techniques that is world famous. Whether you are interested in developing a sound mind or a healthy body, our Dojo offers unlimited classes six days a week. We have separate classes for different levels and separate classes for children and adults.

http://shotojuku.com/

We alsoofferteam training classes in Kata, Kumite and Kobudo forour competition team.

At this point, you are going to use the following information to put together.

1.  Entity relationship diagram

2.  Create a Database with SQL Plus Oracle

Requirements and Business Rules (Follow steps done in class)

a. Create tables (5 tables)

b. Include Primary Keys

c. Alter table with FK

b. Normalize Tables.

c. Insert into tables (2 inserts each table)

DON’T FORGET TO SPOOL

Don’t forget that tables have CONSTRAINTS, they must NOT be violated

Attributes examples
StudentID

StudentName
Phone
Address
E-mail Address

Part B answers the queries using LeeBooks.
1) [3 points] SELECT DISTINCT column_name, column_name
FROM table_name;

Example: SELECT DISTINCT CustLastName, CustPhone
FROM CUSTOMERS;

2) [3 points] SELECT * FROM Table
WHERE column_name='Value'
AND column_name='Value';

Example SELECT * FROM Customers
WHERE City='Berlin'
OR City='München';

3) [3 points] SELECT * FROM Table
WHERE Column_Name='Value'
AND (Column_Name ='Value' OR Column_Name ='Value');

SELECT * FROM Customers
WHERE Country='Germany'
AND (City='Berlin' OR City='München');

4) [3 points] SELECT column_name, column_name
FROM table_name
ORDER BY column_name ASC|DESC, column_name ASC|DESC;

5) [3 points] UPDATE table_name
SET column1=value1, column2=value2,...
WHERE some_column=some_value;

6) [3 points] SELECT column_name(s)
FROM table_name
WHERE column_name LIKE pattern “%A_”;

7) [3 points] SELECT column_name(s)
FROM table_name
WHERE column_name BETWEEN value1 AND value2;

8) [3 points]SELECT column_name(s), column_name(s)
FROM table1, table 2
WHERE table1.column_name = table1.column_name;

9) [3 points] SELECT column_name(s), column_name(s), column_name(s)

FROM table1, table2, table3

WHERE table1.column_name = table2.column_name

AND table2.column_name = table3.column_name

10) [3 points] SELECT column_name AS alias_name
FROM table_name;

11) [4 points] SELECT AVG(column_name) FROM table_name;

12) [4 points] SELECT COUNT(column_name) FROM table_name;

13) [4 points] SELECT MAX(column_name) FROM table_name;

14) [4 points] SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name;

15)[ 4 points] SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name
HAVING aggregate_function(column_name) operator value;