Below are the requirements for my sql assignment
sql_assignment_1_spring_2019.docx
Unformatted Attachment Preview
50 points Using SQL Part One
Name __________________________
Directions:
Throughout this document, answer anything highlighted in yellow. The point is really not the questions,
but to have you run some sample queries so that you can learn. At the end, you will write a few queries
by yourself without me so be sure to observe what is going on and not just copy and paste. If you have
prior experience with SQL, please let me know so I can have you do some advanced work.
Getting Started
Click on https://login.oracle.com/oaam_server/login.do
And create an account. Fill in the form using your ulm e-mail address to create an account.
Once you have received the e-mail to validate your account, continue…
Running the Script to Create a Database
Click on the following link or open a browser and copy and paste this link into it.
https://livesql.oracle.com/apex/f?p=590:41:907039963945:::41:SHAREKEY:GFBUGDMXSFZBVY3KETX8LE
14D
Follow the directions to login.
You should then see the following screen.
In the upper right hand corner, click on the Run Script box (it is red).
1
You should see a screen similar to this one with the Success message:
The script you ran contains a series of 260 SQL statements to create tables in the database and insert
data into the tables. Feel free to examine the SQL code by scrolling down. When are you are finished
examining the code, close the pop-up window.
The database that is created has the following schema. The lines show the relationship among the
tables. For example, if you look at the line between the PROFESSOR and CLASS tables, you see 1 beside
the PROFESSOR table and ∞ (the symbol for infinity) beside the CLASS table. This means that each
professor can be associated with many classes. The relationship between the PROFESSOR and CLASS
tables is said to be 1:N and is referred to as “one to many”.
Based on the schema above, fill in the blanks with the word “one” or “many”:
_______ department(s) can be associated with _________ professor(s).
2
Running Queries
We will start by running some queries to explore the contents of some of the tables and running some
simple one table queries.
1.
Click on SQL Worksheet tab on the left column:
2.
Let’s start by listing all columns (fields) and all rows (records) in the employee table by entering
the following code
select * from employee
3.
Select run in the upper right corner in the red box. Your screen should look similar to the screen
below after you run the query.
Scroll down to the bottom of the list of the results. You should see “xx rows are selected.”
How many rows are selected/returned? __________
This is ALL columns (because we used *) and all rows in the employee table.
Note: It is easy to change SQL code without starting from scratch.
4.
Write the SQL to list all rows (records) in the employee table who have the jobcode of PROF by
adding this line under the existing line:
where EMP_JOBCODE = ‘PROF’
Select run
Your screen should be similar to the one below:
3
How many rows are returned? _________
5.
Change the jobcode of interest to cust by changing PROF to cust.
Run the query.
Why are no rows returned? ____________________________________
6.
Correct the query and run it.
How many rows are returned? ________________
7.
The administration is interested in cleaning up the records and wants to know which students
have missing phone numbers. Start by listing all columns and all rows in the STUDENT table. Write the
code yourself!
What code did you write? ___________________________
How many rows are returned? ________________
8.
The next step is to determine which student records have missing data (no data or NULL data) in
the STU_PHONE column. To do so, type this code:
select * from student
where STU_PHONE IS NULL
How many rows are returned? ________________
4
Look at the STU_PHONE field in each of the records returned. Is there another way to write this query?
You can try it, but the answer is No. Read the PowerPoint to discover why!!
9.
Regarding the previous query, we only need the following columns – stu_lname, stu_initial, and
dept_code. To display only these three columns (fields), replace the ‘*’ with the fields of interest.
Change and run the query. You should see something like the following. Note that the order the
columns are displayed matches the order in which you include the fields in the select clause:
Add the student number field as the first field to the column list and run the query again. Provide the
screen shot of the query and result below.
What if you don’t know the exact field name to add? (select * from tablename will work, but use with
caution!! one could look in the schema)
Would you run a select * query in the corporate world? Probably not – there are millions and billions of
records and running such a query can be a DISASTER!!
10.
Often times queries return multiple rows containing the same data when we only want to know
the different entries. The keyword DISTINCT comes in handy in these situations.
5
Consider the student table (assume it has 7000+ rows!) (select * from student) and look at the stu_class
column. See all the multiple entries of Fr, So, Jr, and Sr? Run the following code to obtain the results
below. This type of query comes in handy when trying to clean up data – what if someone entered Sp
for sophomore instead of So? We would see Sp in the list and know that! Try running the code without
the word DISTINCT to see what happens!
Write the SQL to return only different rows of jobcode in the EMPLOYEE table. Include a screenshot of
the code and results below. Hint: You have to use the correct column name for jobcode!!!! You should
see 4 rows returned.
11.
As you may have determined by now, it is possible to combine each of the items we have used
thusfar. Another option is to sort (using ORDER BY) the data in either ascending or descending order
(ASC/DESC). Ascending order is the default (if you don’t specify ASC or DESC, ASC is assumed). The fun
thing about this keyword is that the columns used to sort the data do not have to be displayed in the
results (in other words, they do not have to be in the SELECT statement! Walk yourself through this
example:
select * from employee
where EMP_JOBCODE = ‘PROF’
order by emp_num desc
Notice the order of the results.
What is the employee first name of the first record of the results? ________________
Now change the select part – remove * and include the fields for just employee first name and
employee last name. Run the code. The order is still the same even though jobcode and employee
number are no longer displayed. Notice the first name of the first record is still the same.
select emp_lname, emp_fname from employee
where EMP_JOBCODE = ‘PROF’
order by emp_num desc
6
Try these queries – notice that the results are sorted on two fields. In the first one, the results are sorted
in descending order by last name and ascending order by first name. In the second one, both are in
ascending order.
select emp_lname, emp_fname, emp_initial from employee
where EMP_JOBCODE = ‘PROF’
order by emp_lname DESC, emp_fname
select emp_lname, emp_fname, emp_initial from employee
where EMP_JOBCODE = ‘PROF’
order by emp_lname, emp_fname
Scroll down to the last name of smith. There are 3 employees with this last name. What do you notice
about the second and third “Smith”? ________________________________________________
You have noticed the middle initial is important in the sorting. Correct the code above (write it below) to
sort the employees by last name, first name, and then middle initial (all in ascending order.)
7
Queries for you to write on your own. For these first 6, copy and paste
the SQL code from Oracle – don’t just supply the results!
Supply the query for each of the situations below. Note in some cases the column heading name is
spelled out and not supplied exactly as it is in the table. Be sure to sort when required to earn all points.
1.
Provide a list of all columns and all rows in the EMPLOYEE table who have the jobcode of CLRK.
Sort by last name in ascending order.
2.
Write the SQL Code to list all columns and all rows in the STUDENT table who have a Stu_class of
Sr. Sort the resulting list in descending order by student hours.
3.
Write the SQL code to determine how many students do not have a middle initial. Sort by
student number.
4.
Write the SQL code to determine the number of students who are transfer students. Sort by last
name and first name. Hint: What has to be true for transfer students? Look at the column
names!
5.
Supply the SQL code to list the Professors who have Ph.D. degrees (Hint: you might want to
write a query to become familiar with the data BEFORE writing the query. This does not use the
employee or the student table.)
6.
Obtain a listing of the different (unique) types of high degrees (none should be repeated)
obtained by professors from the PROFESSOR table. None of the results should appear more than
once in your result. Sort by degree type in descending order.
Answer this question:
Run this query and do your best to explain what is happening. Take a few minutes to look at the data in
each of the tables used in the query. I’ll bet you can figure it out!
select student.emp_num, student.stu_lname, employee.emp_lname from student, employee
where student.emp_num = employee.emp_num
order by student.emp_num
LIKE Operator
Read through the following and complete the table below by writing the correct query.
Recall the WHERE clause is used to filter records. The LIKE operator is used in a WHERE clause to search
for a specified pattern in a column. There are two wildcards used in conjunction with the LIKE operator:
% – Represents zero, one, or multiple characters
_ – Represents a single character
MS Access uses asterisk (*) instead of percent (%) and question mark (?) instead of the underscore (_).
8
The wildcards can be and usually are used in combinations. Run each of these examples in the table
below with “SELECT * from STUDENT _____” where _____ is the WHERE clause in the table below.
The first one would be:
SELECT * from STUDENT WHERE Stu_Fname LIKE ‘A%’;
Notice if you leave the % sign off, no records are returned as it is looking for first names of exactly A.
Remember anything in quotes is case sensitive.
12. Complete the table by entering the number of rows returned in each case. Be careful as they are not
all using the Fname field! Play with these – they are case sensitive! All will have at least one row/record
returned. If you answer zero, it is incorrect.
LIKE Operator
Description
Number of rows returned
WHERE Stu_Fname LIKE ‘A%’
Finds any values that starts with “A”
WHERE Stu_Fname LIKE ‘%a’
Finds any values that ends with “a”
WHERE Stu_Fname LIKE ‘%or%’
Finds any values that have “or” in any
position
WHERE Stu_Lname LIKE ‘_r%’
Finds any values that have “r” in the
second position
Finds any values that starts with “A” and
WHERE Stu_Fname LIKE ‘A_%_%’ are at least 3 characters in length
WHERE Stu_Lname LIKE ‘A%z’
Finds any values that starts with “A” and
ends with “z”
Using the employee table, write the following 4 queries to show you understand the LIKE operation to
display records that match the following patterns. Display all columns in the employee table for
each query. For each query below, include the query and the results and the number of rows
that were returned.
a. List all records with the first name starting with “D”.
b. List all records with the last name ending with “n”.
c. List all records with the first name including “ll” in any position. Note: that is two lower
case letter “L”
d. List all records with last name that starts with “W” and ends with “n”
9
Turn in this complete document with your name entered, responses to each of the items in yellow, and
your screenshots of the 10 queries and any other responses inside the document.
10
…
Purchase answer to see full
attachment
