T-Mobile Wi-Fi * 49% 10:33 instructure-uploads.s3.amazonaws.com Background: You

Question

T-Mobile Wi-Fi * 49% 10:33 instructure-uploads.s3.amazonaws.com Background: You will place all your answers within the remainder of this document. It is to your advantage to copy your SQL commands and paste them into this document. Simply capturing your commands as an image will not allow you to run them over again if it becomes necessary. SQL Results, however, should be captured as an image using, for example, the Snipping Tool. As you prepare to respond to the final questions from the Amy Miles, the CEO of Regal Theaters, review the MOVIE ERD (Entity Relationship Diagram) that you generated in fulfillment of Assessment 1. The MOVIE ERD is shown below Movie Database Entity Diagram (ERD) DATE Task 1.(15 pts) Regal Theaters also wants to be able to search for a movie title without worrying about case sensitivity. For example the query "SELECT FROM MOVIE WHERE TITLE JAWS" returns "no data found" since the title of the movie is stored in the database as 'Jaws', not JAWS Use the UPPER function (reference section "Character Functions" in your eBook, page 229) in order to demonstrate your solution to not worring about case sensitivity in the WHERE clause. Although Figure 8-1 is a good example of using the UPPER function in the SELECT clause, you will need to use the UPPER function in the WHERE clause. Your specific task is to list all the attributes of the Jaws movie assuring that it is not case sensitive. Your SCL command will be of the for SELECT FROM MOVIE WHERE 22222(222221JAKS Note that your results for MOVIE ID and DIRECTOR ID could be difierent.

Explanation / Answer

1)

SELECT * FROM MOVIES WHERE UPPER(TITLE) = 'JAWS' ;

Here we are mentioning in WHERE clause that we are using upper case letters for searching. The SQL understands that these letters may be present also in lower case under the column named TITLE.

or alternatively we can also use lower as shown:

SELECT * FROM MOVIES WHERE LOWER(TITLE) = 'jaws' ;

Here we are mentioning in WHERE clause that we are using lower case letters for searching. The SQL understands that these letters may be present also in upper case under the column named TITLE.

These queries make the search case insensitive.

2)

SELECT M.TITLE , CONCAT( D.FIRST_NAME , ‘ ’ , D.LAST_NAME) AS DIRECTOR_NAME, CONCAT(S.FIRST_NAME, ‘ ’ ,S.LAST_NAME) AS STAR_NAME

FROM MOVIE M

FULL JOIN DIRECTOR D ON M.DIRECTOR_ID = D.DIRECTOR_ID

FULL JOIN MOVIE_STAR MS ON M.MOVIE_ID = MS.MOVIE_ID

FULL JOIN STAR S ON MS.STAR_ID = S.STAR_ID

ORDERBY(M.TITLE);

Concat is used to concatenate two columns and As is used to rename the column in the resulting table. The delimiter used is space between First_name and Last_name. Instead of using entire table name to refer the column names, we can use short notations of those tables as shown above. Join is used to join all the tables together and extract the results. On is used to relate the columns based on which those tables are to be joined. It can be the primary key or foreign key of the tables. Orderby will by default sort the result in ascending order.

Related Questions

Navigate

• Browse (All)
• Browse T
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.