amployee iname, address code, title min salary, max salary dob i) Display the first s letters of each employees' name (consider last names) (7 points) SELECT CONCAT -WS(", fname , Iname) AS Fullname FROM employee WHERE Fullname LIKE __ g) How old 'Adam Smith' is? Display your output without any decimal points (8 points) ()4. Answer the following questions by Linear Algebra using the tables given in Q4.(Learning outcome: a) List all employees whose job code is Sales Rep and salary greater than 3000 (4 points). b) List the fhame and Iname of all employees whose salary equals to maximum salary (6 points).

Question

amployee iname, address code, title min salary, max salary dob i) Display the first s letters of each employees' name (consider last names) (7 points) SELECT CONCAT -WS(", fname , Iname) AS Fullname FROM employee WHERE Fullname LIKE __ g) How old 'Adam Smith' is? Display your output without any decimal points (8 points) ()4. Answer the following questions by Linear Algebra using the tables given in Q4.(Learning outcome: a) List all employees whose job code is Sales Rep and salary greater than 3000 (4 points). b) List the fhame and Iname of all employees whose salary equals to maximum salary (6 points).

Answer 1

i) To display the first s letters of each employee's name, you can use the following SQL query:```sqlSELECT SUBSTRING(Fullname, 1, 5 ShortnameFROM employeeWHERE Fullname LIKE '%Adam%'```This query uses the `SUBSTRING` function to extract the first 5 characters of the `Fullname` column, which contains the employee's name. The `LIKE` operator is used to search for all employees whose names contain the substring 'Adam'.g) To find out how old 'Adam Smith' is, use the following SQL query:```sqlSELECT DATE_PART('year', AGE(CURRENT_DATE - DOB)) AS AgeFROM employeeWHERE Iname = 'Adam' AND Lname = 'Smith'```This query uses the `AGE` function to calculate the difference between the current date and the `DOB` (date of birth) of the employee. The `DATE_PART` used to extract the year part of the age. The `WHERE` clause is used to filter the results to only include employees with the name 'Adam Smith'.4a) To list all employees whose job code is Sales Rep and salary greater than 3000, you can use the following SQL query:```sqlSELECT * FROM employeeWHERE JobCode = 'Sales Rep' AND Salary > 3000```This query uses the `WHERE` clause to filter the results to only include employees whose `JobCode` is 'Sales Rep' and whose `Salary` is greater than 3000.4b) To list the `fname` and `Iname` of all employees whose salary equals to maximum salary, you can use the following SQL query:```sqlSELECT fname, InameFROM employeeWHERE Salary = (SELECT MAX(Salary) FROM employee)```This query uses the `WHERE` clause the results to only include employees whose `Salary` is equal to the maximum salary. The maximum salary is obtained by using a subquery to select the maximum value from the `Salary`

Soru

Çözüm

Cevap