1. Using the l_employees table, add 10 dollars to everyone’s credit limit. If th
ID: 3626672 • Letter: 1
Question
1. Using the l_employees table, add 10 dollars to everyone’s credit limit. If they don’t have a credit limit, assume that it is zero.2. Show the primary key column(s) of the l_lunches table. Show all the SQL statements used. Lunch_id is the primary key.
3. Find the employee_id, first_name, last_name and number of days that everyone has been working with the company from the l_employees table. If someone doesn’t have a hire_date assume it was the first of this year.
4. Create a new view for the l_suppliers table. Call this view l_suppliers_view. This view will have all the columns of the l_suppliers table, plus a new column called abrv_nm. The abrv_name is the supplier_name column up to the first space. So for example, a name of Alice & Ray’s Restaurant will have an abrv_nm of ‘Alice’
5. List the supplier_id, product_code, and description of all columns in the l_foods table. Show only the first three letters of the first word, and all of the last word. For example, ‘FRESH SALAD’ would become ‘FRE SALAD’
6. Find the average credit limit, minimum credit, and maximum credit of employees for each department from the l_employees table. Show average credit limit, minimum credit, maximum credit, and dept_code in your results.
7. Find all the suppliers from the l_foods table whose average price of food is greater than $2.50
8. From the l_employees table, count the number of employees who work in each department. Sort the rows of the result table by the dept_code;
9. From the l_employees table, list all the different combinations of dept_code and credit_limit. Count the number of employees who are in each of these categories. Sort the rows by dept_code and then by credit_limit. Exclude the ‘SHP’ department in your results. Show only the dept_code, with a credit limit of over 12.50.
10. From the l_lunches table, count the number of lunches served each day. (The day the lunch is served is the lunch_date.)
11. From the l_employees table, count the number of employees hired in each year.
12. From the l_employees table, count the number of employees hired in 1999.
13. Find the date when you will be 15,000 days old. Show the date in MM/DD/YYYY format.
14. Find the number of days between July 29, 1969 and January 1, 2004.
Explanation / Answer
6)SQL> SELECT MAX(Credit_limit)As maximum,AVG(Credit_limit)As average,
MIN(Credit_limit)As minimum from l_employees ;
From suppliers s,l_foods l
Where AVG(l.price)>$2.50 and s.supplier_id=l.supplier_id;
Note: Only one question per post, as per the Cramster rule. Please post remaining Questions in other post.
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.