Description

Instructions:

·         Do your own work: This is an individual assignment and you must do your own work and create your own SQL statements.  If you are caught cheating on this or using someone else’s work, you will receive a zero on this assignment and be reported to the Dean of Students. Also, this homework prepares you for the exam coming up so doing the work now will help you learn and do well on when it counts more.

·         What to turn in

o   Clearly separate your code for each question. Save your code into one SQL file with the naming format: LastName_FirstName_UTEid.  Please make sure the lastname and firstname you use matches what is in Canvas.

o   Save your file either as a .sql file or as a .txt file. If you need help doing this, refer to the page linked in the Canvas assignment. Files saved in a different format will be 50% and files in a different format that cannot be read into SQL (example: PDF) will result in a 0%.

o   Submit your .sql file on Canvas before the deadline. Late submissions receive 50% off. No submissions will be accepted 24 hours after the deadline.

o   Do not include the DDL in your submission. If you do, you will lose 5 points. Only provide SQL with comments and nothing else.  Do this going forward on all other assignments unless noted.

·         The SQL problems below will be based on the DDL script that is posted on the Canvas instructions.  Download that script and run it before you start.

Problems:

1.      Write a SELECT statement that returns following columns from the User table: first_name, last_name, email, birthdate. Then, run this statement to make sure it works correctly.

Add an ORDER BY clause to this statement that sorts the result set by last name in ascending order. Then, run this statement again to make sure it works correctly. This is a good way to build and test a statement, one clause at a time.

2.      Write a SELECT statement that returns one column from the User table named user_full_name that combines the first_name and last_name columns.

Format this column with the first name, a space, and last name like this:

John Doe

Sort the result set by first name in descending sequence.

Return only the users whose last name begins with letters of K, L, and M.

3.      Write a SELECT statement that returns these columns from the Videos table: title, subtitle, upload_date, views, and likes. Return only the rows with an upload date between the beginning of this year and last week (i.e. between ’01-Jan-20’ and the date one week before this assignment is due). Use the BETWEEN operator. Sort the result set in descending sequence by the upload_date column.

4.      Create a duplicate of the previous query but this time update the WHERE clause to use only the following operators (<, >, <=, or >=). Keep the rest of the query the same.

5.      Write a SELECT statement that returns these column names and data from the Video table:        

video_id                      The video_id column

video_size                   The video_size column but with a column alias of video_size_MB

likes                             The likes column with a column alias of Likes_Earned

video_length                The video_length column with a column alias of video_length_sec

video_length_min        The is a calculated column based on the logic below*.

*Divide the video_length (currently in seconds) by the number of seconds in a minute to find video length in minutes.

Use the ROWNUM pseudo column so the result set contains only the first 3 rows from the table.

Sort the result set by the column alias Likes_Earned in descending order.