COP

# SQL_3 GRADED.docx - Mary Doolin SQL_3 Homework COP4710...

• Homework Help
• 19
• 100% (3) 3 out of 3 people found this document helpful

This preview shows pages 1–5. Sign up to view the full content.

Mary Doolin SQL_3 Homework COP4710 9.2/10 ********************************** Ex 3-1 ************************************ Create two tables, Stu(sname, majorcode) and Major(majorcode, majordesc) with the data shown. Use VARCHAR2(2) for codes and appropriate data-types for the other attributes. SQL> CREATE TABLE Stu (sname VARCHAR(20), majorCode VARCHAR(2)); CREATE TABLE Stu (sname VARCHAR(20), majorCode VARCHAR(2)) * ERROR at line 1: ORA-00955: name is already used by an existing object SQL> CREATE TABLE Stud (sname VARCHAR(20), majorCode VARCHAR(2)); Table created. SQL> CREATE TABLE Major (majorCode VARCHAR(2), majorDesc VARCHAR(20)); Table created. ********************************** Ex 3-1a ************************************ Display the Cartesian product (no WHERE clause). Use SELECT * .... How many rows did you get? How many rows will you always get when combining two tables with n and m rows in them (Cartesian product)? SQL> SELECT * FROM Stud, Major; SNAME MAJORC MAJORC MAJORDESC ----------- ------------ ------------ ------------------- Jones CS AC Accounting Smith AC AC Accounting Evans MA AC Accounting Adams CS AC Accounting Jones CS CS Computer Science Smith AC CS Computer Science 1

This preview has intentionally blurred sections. Sign up to view the full version.

Mary Doolin SQL_3 Homework COP4710 Evans MA CS Computer Science Smith AC MA Math Evans MA MA Math Adams CS MA Math 12 rows selected. The Cartesian product will always be the amount of rows in one table multiplied by the amount of rows in the second table. 4*3=12. ********************************** Ex 3-1b ************************************ Display an equi-join of Stu and Major on majorCode (show this both ways: first using an appropriate WHERE clause; and second, using ANSI SQL standard syntax). Use table aliases. How many rows did you get? SQL> SELECT * FROM Stud INNER JOIN Major ON Stud.majorCode = Major.majorCode; SNAME MAJORC MAJORC MAJORDESC ----------- ------------ ------------ ------------------- Jones CS CS Computer Science Smith AC AC Accounting Evans MA MA Math Adams CS CS Computer Science 4 rows selected. 2
Mary Doolin SQL_3 Homework COP4710 ********************************** Ex 3-1c ************************************ Leave off the column qualifiers (the aliases) on the equi-join in step b. What do you get? This will give an error because of ambiguous column names. SQL> SELECT * FROM Stud INNER JOIN Major; SELECT * FROM Stud INNER JOIN Major * ERROR at line 1: ORA-00905: missing keyword ********************************** Ex 3-1d ************************************ Use the COUNT(*) function instead of SELECT * in the query. Use COUNT to show the number of rows in the result set of the equi-join and the Cartesian product. Do the equi-join first with COUNT, then comment out the WHERE clause for the second answer (put -- in front of the word WHERE). SQL> SELECT COUNT (*) FROM Stud INNER JOIN Major ON Stud.majorCode = Major.majorCode; COUNT(*) ---------- 4 SQL> SELECT COUNT (*) FROM Stud INNER JOIN Major -- ON Stud.majorCode = Major.majorCode; SELECT COUNT (*) FROM Stud INNER JOIN Major -- ON Stud.majorCode = Major.majorCode * ERROR at line 1: ORA-00905: missing keyword 3

This preview has intentionally blurred sections. Sign up to view the full version.

Mary Doolin SQL_3 Homework COP4710 ********************************** Ex 3-1e ************************************ Add two more major codes to the Major table as follows: <’IT’, ‘Information Technology’> and <’ST’, ‘Statistics’>. Display all the student names (snames) and majorDescs, but I would also like to see all the majors listed, even if there are no students taking that major. Show your query in both ways: first, using ANSI SQL standard syntax, and then using Oracle’s driving table(+) concept.
This is the end of the preview. Sign up to access the rest of the document.

{[ snackBarMessage ]}

### What students are saying

• As a current student on this bumpy collegiate pathway, I stumbled upon Course Hero, where I can find study resources for nearly all my courses, get online help from tutors 24/7, and even share my old projects, papers, and lecture notes with other students.

Kiran Temple University Fox School of Business ‘17, Course Hero Intern

• I cannot even describe how much Course Hero helped me this summer. It’s truly become something I can always rely on and help me. In the end, I was not only able to survive summer classes, but I was able to thrive thanks to Course Hero.

Dana University of Pennsylvania ‘17, Course Hero Intern

• The ability to access any university’s resources through Course Hero proved invaluable in my case. I was behind on Tulane coursework and actually used UCLA’s materials to help me move forward and get everything together on time.

Jill Tulane University ‘16, Course Hero Intern