SQL, normalization, and tables

1. A tutoring company wants to set up their database. They are trying to keeptrack of the following: student ID, name, grade level, the subject they need
tutoring in and their contact and payment information. They also want to keep
track of the tutors’ information such as their ID, name, subject matter expertise
and times available and rate. They will like to keep track of which student is
assigned to which tutor and their meeting schedule as well.
(5 points)*List all of the data that needs to be kept track of for the scenario above in a 1NF
(Flat File) table
(20 points)*Use the normalization technique and form the 3NF tables
(25 points)*Write out the Create Table command in SQL for creating the database you
have designed, make sure to use the right DATATYPES, CONSTRAINTS, and COSTRAINT
REFERENCES.
Create Table #####
(
);
(20 points) *Write one SQL Insert into Values ( ); command for each table in your
database
____________________________________________________________________________________
2. A new veterinary clinic will like to set up their database. The clinic will like to
keep record of their customers such as: pet name, pet type, pet age, pet weight,
owner name, owner address, owner contact. Also the clinic will like to keep
track of the veterinarian name, specialty and schedule. They will like to keep
track of the customer appointment date, what the appointment is for, and how
much it will cost.
(5 points)*List all of the data that needs to be kept track of for the scenario above in a 1NF
(Flat File) table
(20 points)*Use the normalization technique and form the 3NF tables
(25 points)*Write out the Create Table command in SQL for creating the database you
have designed, make sure to use the right DATATYPES, CONSTRAINTS, and COSTRAINT
REFERENCES.
Create Table #####
(
);
(20 points) *Write one SQL Insert into Values ( ); command for each table in your
database
___________________________________________________________________________
3. A hair salon will like to set up their database. They will like to keep track of their
customers (you can decide on what information to keep track of for the
customers). They will like to keep track of their stylists (you can decide what
information to keep track of for the stylists). They will like to keep track of the
schedule between the customers and the stylists. (Payment information is not
needed).
(5 points)*List all of the data that needs to be kept track of for the scenario above in a 1NF
(Flat File) table
(20 points)*Use the normalization technique and form the 3NF tables
(25 points)*Write out the Create Table command in SQL for creating the database you
have designed, make sure to use the right DATATYPES, CONSTRAINTS, and COSTRAINT
REFERENCES.
Create Table #####
(
);
(20 points) *Write one SQL Insert into Values ( ); command for each table in your
database
__________________________________________________________________________
For Questions 4 – 11: Please refer to the 3 tables below (DEPTARTMENTS,
EMPLOYEES, SALARYGRADES)
DEPTARTMENTS
DEPTNO DNAME
LOC
10
ACCOUNTING LAS ANGELES
20
RESEARCH
HOUSTON
30
SALES
NEW YORK
40
OPERATIONS WASHINGTON DC
EMPLOYEES
EMPNO ENAME
JOB
MGR HIREDATE SAL COMM DEPTNO
7369
WU
CLERK
7902 12/17/1980 800
7499
MITCHELL
SALESMAN 7698 2/20/1981 1600 300
30
7521
JOHARI
SALESMAN 7698 2/22/1981 1250 500
30
7566
OBI
MANAGER 7839 4/2/1981
20
7654
AKBAR
SALESMAN 7698 9/28/1981 1250 1400
30
7698
SMITH
MANAGER 7839 5/1/1981
2850
30
7782
JACKSON
MANAGER 7839 6/9/1981
2450
10
7788
DWIGHT
ANALYST
7566 12/9/1982 3000
20
7839
XIO
PRESIDENT
11/17/1981 5000
10
7844
DHIMAN
SALESMAN 7698 9/8/1981
7876
CHOPRA
CLERK
7788 1/12/1983 1100
20
7900
RODRIGUEZ CLERK
7698 12/3/1981 950
30
7902
PEREZ
ANALYST
7566 12/3/1981 3000
20
7934
MILLER
CLERK
7782 1/23/1982 1300
10
9999
ABDUL
CAPTAIN
4/14/2002 6000
50
2975
1500 0
20
30
SALARYGRADES
GRADE LOSAL HISAL
1
900
1200
2
1201
1500
3
1501
2100
4
2101
4000
5
4001
9999
________________________________________________________________
4. (5 points)
Write the SQL query to list all employee information in department 30.
5. (5 points)
Write the SQL query to list employees name, job, and salary that is a manager and has a
salary > $2,000
6. (5 points)
Write the SQL Query to select all employees with an “H” as the second character of their
name. Use a wildcard.
7. (5 points)
Write the SQL Query to show a list of different jobs. Eliminate repeating values.
8. (5 points)
Write the SQL Query to show employee names and salary that has not earned a
commission yet.
9. (5 points)
Write the SQL Query to show the employee name with the maximum salary
10. (5 points)
Write the SQL Query to show what is the difference between the highest and lowest
salary?
11. (5 points)
Write the SQL Query to select employee number, name for all employees with salaries
above the average salary. Use a subquery.