Please confirm this assignment if you're good-natured-natured in SQL!
On vindications which apprehend SQL apprehend your SQL and 5-10 lines of the output.
1. What is SQL?
2. What are leading, singular and exotic key constraints? What is referential rectitude and how does it report to these constraints?
3. Provide the DDL to compel the PEOPLE_TEST consultation vivid underneath.
Name Null? Type
------------------------------- -------- ----
SSN NOT NULL VARCHAR2(9)
LAST_NAME NOT NULL VARCHAR2(20)
FIRST_NAME NOT NULL VARCHAR2(15)
SALARY NOT NULL NUMBER(9,2)
Create the consultation in your schema.
4. Provide the DML to instil this truths into the PEOPLE_TEST consultation.
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. Selected leading_name, conclusive_call enjoin from PEOPLE_TEST in sqlplus. Use shaft enjoin to format your output. Cut and paste the leading 10 lines of the remainder – including your interrogation and the col enjoins into your vindication.
6. What is annul? Interpret the provisions rollback and consign. Does DDL insist-upon a consign? Does DML? Why is consignting present and frequently leading?
7. Engender a consultation SORTA_RICH_PEOPLE_TEST delay the corresponding edifice as PEOPLE_TEST using CTAS. Populate your new consultation singly delay mob who compel further than $65000 in stipend from PEOPLE_TEST. Run the SQL and then evince the SQL and a excellentededed * from SORTA_RICH_PEOPLE_TEST in your vindication. Cut and paste the remainder – including your interrogation into your vindication.
8. You feel stored divers SQL enjoins in an ASCII/text rasp on a floppy (A:\MY_COMMANDS.SQL). You are logged into SQLPLUS. Give the enjoin to run this SQL script rasp. How would you do this in contriver?
9. What is a end? Give two reasons you would engender a end.
10. What is the arrangement catalog or truths glossary? Why do you solicitude? What are the user_*, all_* and dba_* ends?
11. You absence to comprehend the calls of all the consultations you own. Give the SQL enjoin. Say one of the consultations was PEOPLE_TEST. Now you absence to see the consultation PEOPLE_TEST’s edifice. Give the SQL enjoin. Now you absence to see all of the consultation PEOPLE_TEST’s truths. Give the SQL enjoin.
12. You longing to engender a user designated George delay a password of thejungle whose delinquency consultationspace is users and whose imenduring consultationspace is users. Give the SQL.
13. What is a role? Why do we compel roles? Give the SQL to transfer the roles coalesce and supplies to George.
14. What rank of truths is stored in char and varchar2 shafts? How are the two shafts contrariant from each other?
15. How is bound truths stored in the truthsbase? What is the delinquency bound evince format? How can you qualify the delinquency bound evince format?
16. What is a consultation? What is a consultationspace?
17. Altering a consultation’s edifice can be distressing. Provide the DDL to diversify the PEOPLE_TEST consultation as follows if it can be executed in a uncombined enjoin or regular interpret why it can’t be executed in a uncombined enjoin.
a. Increase LAST_NAME to 30.
b. Decrease MIDDLE_NAME to 12.
c. Place a new shaft, TITLE, in the consultation positioned behind MIDDLE_NAME. Compel it VARCHAR2(15).
d. Remove the SALARY shaft.
18. Give the SQL to upbound PEOPLE_TEST so that stipend is increased by 15% for everyone born antecedently Jan 1, 1980. Compel the qualify enduring.
19. Give the SQL to upbound PEOPLE_TEST so that females who compel further than $80000 are deleted from the consultation. Don’t consign, in truth annul the qualify. Give me the enjoins you used.
20. Give the SQL to excellentededed sex and the sum of stipend from PEOPLE_TEST for mob from Indianapolis grouped by sex.
21. Give the SQL to excellentededed sex and the sum of stipend from PEOPLE_TEST grouped by city and sex but singly if the sum of stipend for a sex is important than $150,000.
22. Give the SQL to excellentededed all shafts from PEOPLE_TEST if the stipend is important than 55000 or the sex is male.
23. Give the SQL to excellentededed all shafts from PEOPLE_TEST if the conclusive call begins delay G or O.
24. You absence to comprehend what 345 times 98 plus 97 is. How could SQLPLUS recount you? (Give SQL.)
25. Give the SQL to excellentededed the conclusive call, stipend, stipend delay a 15% elevate and stipend delay a 25% elevate from PEOPLE_TEST. Recall the conclusive two output shafts to elevate_15 and elevate_25.