SELECT E.Lname, S.Lname FROM EMPLOYEE as E, EMPLOYEES WHERE E.Super_ssn -S.Ssn; a. Draw the initial query tree for each of these queries, and then show how the SELECT E.Lname, S.Lname FROM EMPLO...
1. For each query, there are one or more things to do. Some of these queries use SQL constructs we haven't covered in class but that you should be able to figure out the meaning of. a. SELECT C.custid, C.companyname FROM Sales.Customers AS C LEFT OUTER JOIN Sales.Orders as O ON C.custid = O.custid WHERE O.orderid IS NULL Provide the result set generated by this query. One of the result rows should be (22, Customer DTDMN). What does this tell...
Consider EMPLOYEE database. Create the database and run the following queries and write the result you get. What would be the result of the following query (please create the resulting table will all the columns and rows)? SELECT D.Dname, E.Lname, P.Pname FROM DEPARTMENT D, EMPLOYEE E, WORKS_ON W, PROJECT P WHERE D.Dnumber=E.Dno AND E.Ssn=W.Essn AND W.Pno=P.Pnumber ORDER BY D.Dname DESC, E.Lname DESC;
2. (20 points) Consider the following relational schema. The key attributes are bold and underlined. Employee (eid, ename, sal, hobby) * Work_On (eid, did, date) * Department (did, dname, budget) Consider the following query SELECT E.ename, D.budget FROM Employee E, Work_On W, Deptartment D WHERE E.eid W.eid AND W.did - D.did AND D.dname Computer Science' AND W.date 11-13-2018 a) (10 pts) Draw an initial query tree using Cartesian product, selection, and projection. b) (10 pts) Draw a query tree that...
Run a query from the employee table to find the age of the employees and the Day(Ex:Monday) they were born. You may need to update these records if Age is shown as negative. Example: NAME BDATE AGE ------------------------------- ------------------ ---------- Ramesh Narayan Monday 09/15/52 61 Jennifer Wallace Saturday 06/20/31 82
A company maintains data on their employees in a table called employee. Write a query to find the highest paid employee in each department. The output should contain department_name, employee_name, and salary. Exclude employees with no department from your result. Input Format: employee table: Column Type employee_no Integer employee_name String department_no Integer salary Integer manager_no Integer department table: Column Type department_no String department_name String
SQL query: Write an SQL query that will output the last name, employee id, hire date and department from the employee table. Pick only those employees whose department ID is specified in the employee table. If the employee id is 777, name is ABC and hire date is 01-JAN-2016, the output should look as follows - 'Stephen (Employee ID - 777) was hired on the 1st of January, 2016 – Department: 90'. Note - The date should not have preceding...
14 What does the query below do? SELECT MAX(salary)-MIN(salary) AS diff, FROM Employees GROUP BY deptID ORDER BY diff; Displays the range of employee salary for each department, sorted by department id first and then by salary range Displays the range of employee salary for each department, sorted by salary range Displays the range of employee salary for each department, sorted by department id Displays the maximum salary, and minimum salary for each department, sort by department id
How do I combine these 2 queries to produce something like this QUERY 1 SELECT hotel.country, time.year, time.month, COUNT(booking.room_id) as booked FROM booking LEFT JOIN room on room.room_id = booking.room_id LEFT JOIN hotel on room.hotel_id = hotel.hotel_id LEFT JOIN time on booking.time_id = time.time_id GROUP BY hotel.country, time.year, time.month ORDER by hotel.country, time.year, time.month QUERY 2 SELECT hotel.country, time.year, time.month, COUNT(checkout.room_id) as checkedout FROM checkout LEFT JOIN room on room.room_id = checkout.room_id LEFT JOIN hotel on room.hotel_id = hotel.hotel_id LEFT...
1- Consider the following SQL query and related relations SELECT P.Name FROM Players P, Games G WHERE P.Id = G.PlayerId AND P.Game = 'Football' AND G.Season ='2019' where: Players (Id, Name, Game) Games (PlayerId, GameCode, Season) A- Translate the above SQL query to a relational algebra expression. B- Draw the Query tree for above relational algebra expression in part (a). Note: Please no screenshot
Database For this lab you will be using SQL SELECT statements to query your database tables. You will be turning in the results of the following queries: 1. List all Patients and what Bed they are assigned to 2. List all patients who had Treatments and what Treatment they received 3. List all patients who had tests and what Test they had 4. List the employees (doctors, nurses, etc.) who assisted each patient. 5. List all patients in alphabetical order...