Assignment $5$: QueriesSummary:Write queries on the video store database in Access. There is no rubric for this assignment. Each question isworth $12\mathrm{.}5$ points.SetupDownload the video store database $($also available from Content$->$Resources $-$ CIS$125$D$)$ for the first part ofthe assignment. Rename the file YLN$\_$a$5.$accdb, using your last name, e$.$g$.,$ Smith$\_$a$5.$accdb. Execute Accessand open this file. You will probably find it helpful to look at the ERD for it in Resources CIS $125$D$.[100\%]$ Queries in AccessWrite the following queries on your video store database, saving each query with the indicated name $($Q$1,$ Q$2,$etc.$).$ Each query is worth $12\mathrm{.}5$ points. Be sure to write each query exactly as specified and show only thecolumns requested.Q$1$: Produce a list of all movies in the video store, displaying the title and year $($only$).$ Sort by year with thenewest movies first.Q$2$: Select customer accounts which have a credit card on file and have a deposit amount greater than $10.$Display the customer account id and date enrolled $($only$)$ and sort by date enrolled. Hint: This query shouldreturn $2$ rows of data.Q$3$: Show a list of employee name $($given and family$)$ and hire dates. Sort by family name. Hint: You willneed to join the EMPLOYEE and PERSON tables.Q$4$: List in alphabetical order the languages that the movie Das Boot is available in$.$ The query result shouldhave only one column named "Das Boot Languages" and three rows giving the names of the $3$ languages.The video store ERD will be helpful to figure out what $3$ tables are needed, even though you are projectingonly one column. I.e$.,$ the result should look like this:Q$5$: Produce a list of movies that are currently rented out $($i$.$e$.,$ the returned date in the MOVIE$\_$RENTALtable is null$).$ Show the movie title, copy number, and customer account id for the rental. Sort by movie titleand then copy number. Hints: You may need to join $4$ tables together for this query. This query shouldreturn $6$ rows of data.Q$6$: Write a query that shows what the movie rental fees for all rentals in the database would be if reducedby $18\%($e$.$g$.,$ a $$6$ fee would become $$4\mathrm{.}92).$ Use the MOVIE$\_$RENTAL table and display the movie id$,$CIS$125$D$\_$A$5.$docx $20231106$ Page $2$ of $3$transaction id$,$ and the rental fee reduced by $18\%.$ Name this last column "Reduced Rental Fee". Round to $2$decimal places using the Round function.Q$7$: Use aggregates $($on the 'Total:$\text{'}$ line $-$ use Totals button$)$ to show the minimum, maximum, and averageamount paid in sales tax on a customer transaction. The result should have one row that looks like this$($name columns appropriately$)$:Q$8$: Show a list of movies in the database along with the number of languages associated with each movie.Display the movie title $($column name: "Title"$)$ and the number of languages $($column name: "Number ofLanguages"$).$ Sort by the count descending. Hint: You will need to use an aggregate for this,specifically, Count, and you can count the column LANGUAGE$\_$CODE in the MOVIE$\_$LANGUAGEtable.SubmissionUpload your database file containing Q$1$ through Q$8,$ YLN$\_$a$5.$accdb as usual to the assignment folder.Script to copyCREATE TABLE EMPLOYEE $($EMP$\_$NUM varchar$(3),$EMP$\_$LNAME varchar$(15),$EMP$\_$FNAME varchar$(15),$EMP$\_$INITIAL varchar$(1),$EMP$\_$HIREDATE datetime,JOB$\_$CODE varchar$(3),$EMP$\_$YEARS int$)$;INSERT INTO EMPLOYEE VALUES$(\text{'}101\text{'},$'News','John',$\text{'}$G$\text{'},\text{'}2000-11-08\text{'},\text{'}502\text{'},\text{'}4\text{'})$;INSERT INTO EMPLOYEE VALUES$(\text{'}102\text{'},$'Senior','David',$\text{'}$H$\text{'},\text{'}1989-7-12\text{'},\text{'}501\text{'},\text{'}15\text{'})$;INSERT INTO EMPLOYEE VALUES$(\text{'}103\text{'},$'Arbough','June',$\text{'}$E$\text{'},\text{'}1996-12-01\text{'},\text{'}503\text{'},\text{'}8\text{'})$;INSERT INTO EMPLOYEE VALUES$(\text{'}104\text{'},$'Ramoras','Anne',$\text{'}$K$\text{'},\text{'}1987-11-05\text{'},\text{'}501\text{'},\text{'}17\text{'})$;INSERT INTO EMPLOYEE VALUES$(\text{'}105\text{'},$'Johnson','Alice',$\text{'}$K$\text{'},\text{'}1993-02-01\text{'},\text{'}502\text{'},\text{'}12\text{'})$;INSERT INTO EMPLOYEE VALUES$(\text{'}106\text{'},$'Smithfield','William',$\text{'}\text{'},\text{'}2004-06-22\text{'},\text{'}500\text{'},\text{'}0\text{'})$;INSERT INTO EMPLOYEE VALUES$(\text{'}107\text{'},$'Alonzo','Maria',$\text{'}$D$\text{'},\text{'}1993-10-10\text{'},\text{'}500\text{'},\text{'}11\text{'})$;INSERT INTO EMPLOYEE VALUES$(\text{'}108\text{'},$'Washington','Ralph',$\text{'}$B$\text{'},\text{'}1991-08-22\text{'},\text{'}501\text{'},\text{'}13\text{'})$;INSERT INTO EMPLOYEE VALUES$(\text{'}109\text{'},$'Smith','Larry',$\text{'}$W$\text{'},\text{'}1997-07-18\text{'},\text{'}501\text{'},\text{'}7\text{'})$;INSERT INTO EMPLOYEE VALUES$(\text{'}110\text{'},$'Olenko','Gerald',$\text{'}$A$\text{'},\text{'}1995-12-11\text{'},\text{'}505\text{'},\text{'}9\text{'})$;INSERT INTO EMPLOYEE VALUES$(\text{'}111\text{'},$'Wabash','Geoff',$\text{'}$B$\text{'},\text{'}1991-04-04\text{'},\text{'}506\text{'},\text{'}14\text{'})$;INSERT INTO EMPLOYEE VALUES$(\text{'}112\text{'},$'Smithson','Darlene',$\text{'}$M$\text{'},\text{'}1994-10-23\text{'},\text{'}507\text{'},\text{'}10\text{'})$;INSERT INTO EMPLOYEE VALUES$(\text{'}113\text{'},$'Joenbrood','Delbert',$\text{'}$K$\text{'},\text{'}1996-11-15\text{'},\text{'}508\text{'},\text{'}8\text{'})$;INSERT INTO EMPLOYEE VALUES$(\text{'}114\text{'},$'Jones','Annelise',$\text{'}\text{'},\text{'}1993-08-20\text{'},\text{'}508\text{'},\text{'}11\text{'})$;INSERT INTO EMPLOYEE VALUES$(\text{'}115\text{'},$'Bawangi','Travis',$\text{'}$B$\text{'},\text{'}1992-01-25\text{'},\text{'}501\text{'},\text{'}13\text{'})$;INSERT INTO EMPLOYEE VALUES$(\text{'}116\text{'},$'Pratt','Gerald',$\text{'}$L$\text{'},\text{'}1997-03-05\text{'},\text{'}510\text{'},\text{'}8\text{'})$;INSERT INTO EMPLOYEE VALUES$(\text{'}117\text{'},$'Williamson','Angie',$\text{'}$H$\text{'},\text{'}1996-06-19\text{'},\text{'}509\text{'},\text{'}8\text{'})$;INSERT INTO EMPLOYEE VALUES$(\text{'}118\text{'},$'Frommer','James',$\text{'}$J$\text{'},\text{'}2005-01-04\text{'},\text{'}510\text{'},\text{'}0\text{'})$;CREATE TABLE JOB $($JOB$\_$CODE varchar$(3),$JOB$\_$DESCRIPTION varchar$(25),$JOB$\_$CHG$\_$HOUR float$(8),$JOB$\_$LAST$\_$UPDATE datetime$)$;INSERT INTO JOB VALUES$(\text{'}500\text{'},$'Programmer',$\text{'}35\mathrm{.}75\text{'},\text{'}2011-11-20\text{'})$;INSERT INTO JOB VALUES$(\text{'}501\text{'},$'Systems Analyst',$\text{'}96\mathrm{.}75\text{'},\text{'}2011-11-20\text{'})$;INSERT INTO JOB VALUES$(\text{'}502\text{'},$'Database Designer',$\text{'}125\text{'},\text{'}2012-03-24\text{'})$;INSERT INTO JOB VALUES$(\text{'}503\text{'},$'Electrical Engineer',$\text{'}84\mathrm{.}5\text{'},\text{'}2011-11-20\text{'})$;INSERT INTO JOB VALUES$(\text{'}504\text{'},$'Mechanical Engineer',$\text{'}67\mathrm{.}9\text{'},\text{'}2011-11-20\text{'})$;INSERT INTO JOB VALUES$(\text{'}505\text{'},$'Civil Engineer',$\text{'}55\mathrm{.}78\text{'},\text{'}2011-11-20\text{'})$;INSERT INTO JOB VALUES$(\text{'}506\text{'},$'Clerical Support',$\text{'}26\mathrm{.}87\text{'},\text{'}2011-11-20\text{'})$;CIS$125$D$\_$A$5.$docx $20231106$ Page $3$ of $3$INSERT INTO JOB VALUES$(\text{'}507\text{'},\text{'}$DSS Analyst',$\text{'}45\mathrm{.}95\text{'},\text{'}2011-11-20\text{'})$;INSERT INTO JOB VALUES$(\text{'}508\text{'},$'Applications Designer',$\text{'}48\mathrm{.}1\text{'},\text{'}2012-03-24\text{'})$;INSERT INTO JOB VALUES$(\text{'}509\text{'},$'Bio Technician',$\text{'}34\mathrm{.}55\text{'},\text{'}2011-11-20\text{'})$;INSERT INTO JOB VALUES$(\text{'}510\text{'},$'General Support',$\text{'}18\mathrm{.}36\text{'},\text{'}2011-11-20\text{'})$;