Task:

On answers which include SQL include your SQL and 5-10 lines of the output.

1. What is SQL?

2. What are primary, unique and foreign key constraints? What is referential integrity and how does it relate to these constraints?

3. Provide the DDL to make the PEOPLE_TEST table described below.

Name Null? Type

----

SSN NOT NULL VARCHAR2(9)

LAST_NAME NOT NULL VARCHAR2(20)

FIRST_NAME NOT NULL VARCHAR2(15)

MIDDLE_NAME VARCHAR2(15)

SEX VARCHAR2(6)

CITY VARCHAR2(30)

DOB DATE

SALARY NOT NULL NUMBER(9,2)

Create the table in your schema.

4. Provide the DML to insert this data into the PEOPLE_TEST table.

303785523, BROWN, JOHN, JAY, MALE, GREENFIELD,10-JAN-68,35000

313507927, SMITH, LINDA, SUE, FEMALE, INDIANAPOLIS,24-MAR-85,50000

312667717, EDWARDS, NORMAN, HENRY, MALE, GREENFIELD,20-APR-92,85000

420001690, GARCIA, STEVEN, LEE, MALE, INDIANAPOLIS, 17-AUG-65,55000

313456233, MEANS, LEONARD, ROBERT, MALE, GREENFIELD,17-AUG-85,75000

490552223, OSBORNE, MARGARET,NULL , FEMALE, INDIANAPOLIS,23-MAR-87,150000

5. Select first_name, last_name command from PEOPLE_TEST in sqlplus. Use column command to format your output. Cut and paste the first 10 lines of the result - including your query and the col commands into your answer.

6. What is undo? Explain the terms rollback and commit. Does DDL require a commit? Does DML? Why is committing early and often important?

7. Create a table SORTA_RICH_PEOPLE_TEST with the same structure as PEOPLE_TEST using CTAS. Populate your new table only with people who make more than $65000 in salary from PEOPLE_TEST. Run the SQL and then display the SQL and a select * from SORTA_RICH_PEOPLE_TEST in your answer. Cut and paste the result - including your query into your answer.

8. You have stored several SQL commands in an ASCII/text file on a floppy (A:\MY_COMMANDS.SQL). You are logged into SQLPLUS. Give the command to run this SQL script file. How would you do this in designer?

9. What is a view? Give two reasons you would create a view.

10. What is the system catalog or data dictionary? Why do you care? What are the user_*, all_* and dba_* views?

11. You want to know the names of all the tables you own. Give the SQL command. Say one of the tables was PEOPLE_TEST. Now you want to see the table PEOPLE_TEST's structure. Give the SQL command. Now you want to see all of the table PEOPLE_TEST's data. Give the SQL command.

12. You wish to create a user named George with a password of thejungle whose default tablespace is users and whose temporary tablespace is users. Give the SQL.

13. What is a role? Why do we make roles? Give the SQL to grant the roles connect and resource to George.

14. What sort of data is stored in char and varchar2 columns? How are the two columns different from each other?

15. How is date data stored in the database? What is the default date display format? How can you change the default date display format?

16. What is a table? What is a tablespace?

17. Altering a table's structure can be hard. Provide the DDL to alter the PEOPLE_TEST table as follows if it can be done in a single command or just explain why it can't be done in a single command.

a. Increase LAST_NAME to 30.

b. Decrease MIDDLE_NAME to 12.

c. Place a new column, TITLE, in the table positioned after MIDDLE_NAME. Make it VARCHAR2(15).

d. Remove the SALARY column.

18. Give the SQL to update PEOPLE_TEST so that salary is increased by 15% for everyone born before Jan 1, 1980. Make the change permanent.

19. Give the SQL to update PEOPLE_TEST so that females who make more than $80000 are deleted from the table. Don't commit, in fact undo the change. Give me the commands you used.

20. Give the SQL to select sex and the sum of salary from PEOPLE_TEST for people from Indianapolis grouped by sex.

21. Give the SQL to select sex and the sum of salary from PEOPLE_TEST grouped by city and sex but only if the sum of salary for a sex is greater than $150,000.

22. Give the SQL to select all columns from PEOPLE_TEST if the salary is greater than 55000 or the sex is male.

23. Give the SQL to select all columns from PEOPLE_TEST if the last name begins with G or O.

24. You want to know what 345 times 98 plus 97 is. How could SQLPLUS tell you? (Give SQL.)

25. Give the SQL to select the last name, salary, salary with a 15% raise and salary with a 25% raise from PEOPLE_TEST. Rename the last two output columns to raise_15 and raise_25.