Uples to answer the following questions by SQL commands. employee (ssn fname address, city,dob, salary,job code) (job code, job position) a) Concatenate and display the first name and last name of all employees. List them in a descending order according to their salary. (4 points) SELECT CONCAT _WS(", "fname', "Iname') AS FullName FROM employee ORDER BY FullName DESC; b) List all employees first name and last name who was born between 2000 and 2005 (4 points) SELECT fname , Iname FROM employee WHERE BORN BETWEEN 2000 AND 2005; c) List the SSN and salary of all employees who carn more than 5000 Change the column name of salary to MANAGERS. (4 points) SELECT SSN , salary AS MANAGERS FROM employee WHERE MANAGERS >5000; d) Find the total amount of salary each employee will earn at the end of 1 year (4 points) e) List the number of employees in each job. Display those which has less than 3 in the group.(7 points)

Question

Uples to answer the following questions by SQL commands. employee (ssn fname address, city,dob, salary,job code) (job code, job position) a) Concatenate and display the first name and last name of all employees. List them in a descending order according to their salary. (4 points) SELECT CONCAT _WS(", "fname', "Iname') AS FullName FROM employee ORDER BY FullName DESC; b) List all employees first name and last name who was born between 2000 and 2005 (4 points) SELECT fname , Iname FROM employee WHERE BORN BETWEEN 2000 AND 2005; c) List the SSN and salary of all employees who carn more than 5000 Change the column name of salary to MANAGERS. (4 points) SELECT SSN , salary AS MANAGERS FROM employee WHERE MANAGERS >5000; d) Find the total amount of salary each employee will earn at the end of 1 year (4 points) e) List the number of employees in each job. Display those which has less than 3 in the group.(7 points)

Answer 1

a) Concatenate and display the first name and last name of all employees. List them in a descending order according to their salary.```sqlSELECT CONCAT(fname, ', lname) AS FullName, salaryFROM employeeORDER BY salary DESC;```b) List all employees first name and last name who was born between 2000 and 2005.```sqlSELECT fname, lnameFROM employeeWHERE dob BETWEEN '2000-01-01' AND '2005-12-31';```c) List the SSN and salary of all employees who earn more than $5000. Change the column name of salary to MANAGERS.```sqlSELECT SSN, salary AS MANAGERSFROM employeeWHERE MANAGERS > 5000;```d) Find the total amount of salary each employee will earn at the end of 1 year.```sqlSELECT SSN, salary * 12 AS TotalSalaryFROM employee;```e) List the number of employees in each job. Display those which have less than 3 in the group.```sqlSELECT job_code, COUNT(*) AS EmployeeCountFROM employeeGROUP BY job_codeHAVING EmployeeCount < 3;```

Soru

Çözüm

Cevap