Question

1 Approved Answer

Posted on Jul 25, 2024

Looking for 5.1-5.10 5. Create Queries that to do the following: (10 marki). Your queries will be marked based on whether the queries are formulated

Looking for 5.1-5.10

5. Create Queries that to do the following: (10 marki). Your queries will be marked based on whether the queries are formulated correct or not, net based on the records that are retrieved by the queries 5.1. Write a query to display the first name, last name, and email address of employees hired from January 1, 2001, to December 31, 2010. Sort the output by last name and then by first name. 5.2.write a query to day the first name, tastatne phone number, title, and department number of employees who work indepartment number 100 of have the title "CLERK" Sort the output by last name and then by first name 5.3.write a query to display the number of products in each category that have water base Page 26 ITIS FALL 2020 5.4 write query to thplay the department number and most recent employee hire date for each department Sort the output by department number Sample out MONTCENTER w. 230 12 2018 BE333831 w 55. Write a my te diplay the stock pingit.description, type, base, category, and price for all products that we aPROD BASE of water and PRO CATEGORY Sangle output 3. Write a very to the employee unter stramentale, lary from date salary en date and wary amount to emples 1731, 83743 019, or the output by employeumber and salary "om" data 1. We Gary to play they endor de Decor Woo Sony to othed by a corner or 100 w firma TRAN 5.6. Write a query to display the employee number, last name, first name, salary "from" date, salary end date, and salary amount for emplyees 83731, 83745, and 84039. Sort the output by employee number and salary "from" date. Sample output IME NUMRINAME IMPJNAME SAL FROM SALINO 83731 VARGAS am VARGAS I VARGAS 13711 VANGAS SPICE 175 VIR MS SPICE BMS LICER S SPOR 33745 SPICE 31 DICER COLMAN 14099 COLEMAN COLEMAN COLEMAN SHERON SHERON SHERON SHERON DWAIN DWAIN OWAIN DWAIN DWAIN OWAIN DWAIN HANNAH WANNAN HANNAN HANNAH 7/15/2001 7/14/2009 7/14/2010 7/15/2011 w/2/2008 w/2005 /1/2007 w//2000 1/2009 1/1/2010 wa/2011 1/21/2008 27/2009 6/27/2010 2011 SALAMOUR 7/14/2009 7/11/2010 7/14/2011 50 51040 1/1/2005 S600 12/2007 57700 1/2008 6M 1/2009 2/31/2010 7190 11/2011 74140 300 627/2007 471 4/20/2010 511 6/27/2011 5200 5.7.Write a query to display all products supplied by a vendor named "Leal Decor Workshop" 5.8.Write a query to display all products purchased by a customer named "JONAS HOSKINS" Page 3 of 6 FALL 2020 ITIS2P91 5.9.Write a query to display the number (Dept_Nun) and name (Dept_Name) of all departments along with their manager's employee number, first name, last name and email address. 5.10. Write a query to display the details of customers (cust_code, cust_fname, and cust_lname) served by an employee named FRANKLYN STOVER. Display the first and last name of the employee in the result. ITIS2P91 FALL 2020 5.9.Write a query to display the number (Dept_Nun) and name (Dept_Name) of all departments along with their manager's employee number, first name, last name and email address. 5.10. Write a query to display the details of customers (cust_code, cust_fname, and cust_lname) served by an employee named FRANKLYN STOVER. Display the first and last pame of the employee in the result. 6. Create additional Queries (a minimum of three) which you think are relevant. (3 marks) 7. Create at least three reports which you think are important. (3 marks) 8. Overall database design features such as appearance and ease of navigation. (4 marks) 5.5.Write a query to display the SKU (stock keeping unit), description, type, base, category, and price for all products that have a PROD_BASE of water and PROD_CATEGORY of sealer. Sample output RCO DESCENT - PROO TYPE PROO JASE. PROD_CATEGORY PROD PRICH Seater Water Bred for Concrete floor Interior Water Sealer 49.99 5.6.Write a query to display the employee number, last name, first name, salary "from" date, salary end date, and salary amount for emplyees 83731, 83745, and 84039. Sort the output by employee number and salary "from" date. Sample output INOM LUKAME - IMD_FNAME - SAL_FROM SAL IND. SAL AMOUS VARGAS SHERON 7/15/2008 7/14/2009 43740 THI VARGAS SHERON 7/14/2009 7/11/2010 48110 TIL VARGAS SHERON 7/14/2010 7/14/2011 49550 7 VARGAS SHERON . 7/15/2011 51040 DWAIN W2/2005 8/1/2006 56020 5 SPICER UWAN w/2/2005 V2/2007 57700 SICER UWAN 2007 11/2005 670 OWAIN 8/2/2008 11/2009 68550 DWAIN 8/1/2009 7/31/2010 7150 LE DWAIN W/1/2010 11/2011 74140 TER OWAIN 70350 COMAN HANNAR 20/2000 /27/2009 4710 A COLEMAN HANNAH 6/27/2003 26/2010 51170 COLEMAN HANNAH 21/2010 27/2011 52700 HANNAH 2011 54280 5.7.Write a query to display all products supplied by a vendor named "Leal Decor Workshop" 5.8.Write a query to display all products purchased by a customer named "JONAS HOSKINS" 5. Create Queries that to do the following: (10 marks). Your queries will be marked based on whether the queries are formulated correct or not, not based on the records that are retrieved by the queries. 5.1. Write a query to display the first name, last name, and email address of employees hired from January 1, 2001, to December 31, 2010. Sort the output by last name and then by first name. 5.2.Write a query to display the first name, last name, phone number, title, and department number of employees who work in department number 300 or have the title "CLERK I". Sort the output by last name and then by first name. 5.3. Write a query to display the number of products in each category that have a water base. I Page 2 of 6 ITIS2P91 FALL 2020 5.4.Write a query to display the department number and most recent employee hire date for each department. Sort the output by department number. Sample output DEPT_NUM - MOSTRECENT HIRE 200 6/8/2001 250 12/15/2011 280 4/16/2010 12/12/2010 400 1/26/2011 4/26/2011 250 10/22/2011 600 10/2/2011 300 500 5. Create Queries that to do the following: (10 marki). Your queries will be marked based on whether the queries are formulated correct or not, net based on the records that are retrieved by the queries 5.1. Write a query to display the first name, last name, and email address of employees hired from January 1, 2001, to December 31, 2010. Sort the output by last name and then by first name. 5.2.write a query to day the first name, tastatne phone number, title, and department number of employees who work indepartment number 100 of have the title "CLERK" Sort the output by last name and then by first name 5.3.write a query to display the number of products in each category that have water base Page 26 ITIS FALL 2020 5.4 write query to thplay the department number and most recent employee hire date for each department Sort the output by department number Sample out MONTCENTER w. 230 12 2018 BE333831 w 55. Write a my te diplay the stock pingit.description, type, base, category, and price for all products that we aPROD BASE of water and PRO CATEGORY Sangle output 3. Write a very to the employee unter stramentale, lary from date salary en date and wary amount to emples 1731, 83743 019, or the output by employeumber and salary "om" data 1. We Gary to play they endor de Decor Woo Sony to othed by a corner or 100 w firma TRAN 5.6. Write a query to display the employee number, last name, first name, salary "from" date, salary end date, and salary amount for emplyees 83731, 83745, and 84039. Sort the output by employee number and salary "from" date. Sample output IME NUMRINAME IMPJNAME SAL FROM SALINO 83731 VARGAS am VARGAS I VARGAS 13711 VANGAS SPICE 175 VIR MS SPICE BMS LICER S SPOR 33745 SPICE 31 DICER COLMAN 14099 COLEMAN COLEMAN COLEMAN SHERON SHERON SHERON SHERON DWAIN DWAIN OWAIN DWAIN DWAIN OWAIN DWAIN HANNAH WANNAN HANNAN HANNAH 7/15/2001 7/14/2009 7/14/2010 7/15/2011 w/2/2008 w/2005 /1/2007 w//2000 1/2009 1/1/2010 wa/2011 1/21/2008 27/2009 6/27/2010 2011 SALAMOUR 7/14/2009 7/11/2010 7/14/2011 50 51040 1/1/2005 S600 12/2007 57700 1/2008 6M 1/2009 2/31/2010 7190 11/2011 74140 300 627/2007 471 4/20/2010 511 6/27/2011 5200 5.7.Write a query to display all products supplied by a vendor named "Leal Decor Workshop" 5.8.Write a query to display all products purchased by a customer named "JONAS HOSKINS" Page 3 of 6 FALL 2020 ITIS2P91 5.9.Write a query to display the number (Dept_Nun) and name (Dept_Name) of all departments along with their manager's employee number, first name, last name and email address. 5.10. Write a query to display the details of customers (cust_code, cust_fname, and cust_lname) served by an employee named FRANKLYN STOVER. Display the first and last name of the employee in the result. ITIS2P91 FALL 2020 5.9.Write a query to display the number (Dept_Nun) and name (Dept_Name) of all departments along with their manager's employee number, first name, last name and email address. 5.10. Write a query to display the details of customers (cust_code, cust_fname, and cust_lname) served by an employee named FRANKLYN STOVER. Display the first and last pame of the employee in the result. 6. Create additional Queries (a minimum of three) which you think are relevant. (3 marks) 7. Create at least three reports which you think are important. (3 marks) 8. Overall database design features such as appearance and ease of navigation. (4 marks) 5.5.Write a query to display the SKU (stock keeping unit), description, type, base, category, and price for all products that have a PROD_BASE of water and PROD_CATEGORY of sealer. Sample output RCO DESCENT - PROO TYPE PROO JASE. PROD_CATEGORY PROD PRICH Seater Water Bred for Concrete floor Interior Water Sealer 49.99 5.6.Write a query to display the employee number, last name, first name, salary "from" date, salary end date, and salary amount for emplyees 83731, 83745, and 84039. Sort the output by employee number and salary "from" date. Sample output INOM LUKAME - IMD_FNAME - SAL_FROM SAL IND. SAL AMOUS VARGAS SHERON 7/15/2008 7/14/2009 43740 THI VARGAS SHERON 7/14/2009 7/11/2010 48110 TIL VARGAS SHERON 7/14/2010 7/14/2011 49550 7 VARGAS SHERON . 7/15/2011 51040 DWAIN W2/2005 8/1/2006 56020 5 SPICER UWAN w/2/2005 V2/2007 57700 SICER UWAN 2007 11/2005 670 OWAIN 8/2/2008 11/2009 68550 DWAIN 8/1/2009 7/31/2010 7150 LE DWAIN W/1/2010 11/2011 74140 TER OWAIN 70350 COMAN HANNAR 20/2000 /27/2009 4710 A COLEMAN HANNAH 6/27/2003 26/2010 51170 COLEMAN HANNAH 21/2010 27/2011 52700 HANNAH 2011 54280 5.7.Write a query to display all products supplied by a vendor named "Leal Decor Workshop" 5.8.Write a query to display all products purchased by a customer named "JONAS HOSKINS" 5. Create Queries that to do the following: (10 marks). Your queries will be marked based on whether the queries are formulated correct or not, not based on the records that are retrieved by the queries. 5.1. Write a query to display the first name, last name, and email address of employees hired from January 1, 2001, to December 31, 2010. Sort the output by last name and then by first name. 5.2.Write a query to display the first name, last name, phone number, title, and department number of employees who work in department number 300 or have the title "CLERK I". Sort the output by last name and then by first name. 5.3. Write a query to display the number of products in each category that have a water base. I Page 2 of 6 ITIS2P91 FALL 2020 5.4.Write a query to display the department number and most recent employee hire date for each department. Sort the output by department number. Sample output DEPT_NUM - MOSTRECENT HIRE 200 6/8/2001 250 12/15/2011 280 4/16/2010 12/12/2010 400 1/26/2011 4/26/2011 250 10/22/2011 600 10/2/2011 300 500