1. Write an SQL query to display course ID, course name and student name right outer joined student with course table using student id. 2. Write an SQL query to display course ID and student name joining student table and course table using student id column for students whose age<19. 3. Write an SQL query to display the student id, age and course id from the student table joined with course table for English course. 4. Write an SQL query to student id, name, age, course id, course name, level name of the student table full outer joined with course table for the Maths course.

1. Write an SQL query to display course ID, course name and student name right outer joined student with course table using student id. 2. Write an SQL query to display course ID and student name joining student table and course table using student id column for students whose age<19. 3. Write an SQL query to display the student id, age and course id from the student table joined with course table for English course. 4. Write an SQL query to student id, name, age, course id, course name, level name of the student table full outer joined with course table for the Maths course.

Database System Concepts

7th Edition

ISBN:9780078022159

Author:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan

Publisher:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan

Chapter1: Introduction

Section: Chapter Questions

Problem 1PE

See similar textbooks

Similar questions

What use cases are best suited for the CROSS APPLY function in SQL?
Question 18 Suppose you have a table called D with the schema/header (c1 TEXT, c2 TEXT, dist REAL).c1 and c2 represent the names of cities and dist represents the distance between them in miles. Below are several attempts to write a SQL query to get the distances of all three-city trips. (That means they start in one city, travel to second city, and then end in a third city.) Which of the following queries will work to retrieve this information? (Select all correct answers; there may be more than one.) SELECT D.c1,D.c2,E.c2,D.dist FROM D , D as E WHERE D.c2=E.c1 SELECT c1,c2,c2,dist+dist FROM D,D WHERE c1=c2 SELECT D.c1,D.c2,E.c2,D.dist+E.dist FROM D, D as E WHERE D.c2=E.c1 SELECT D.c1,D.c2,E.c2,x FROM D, D as E WHERE x=D.dist+E.dist SELECT D.c1,D.c2%3DE.c1,E.c2 FROM D, D as E WHERE D.dist+E.dist O This is not a task that can be solved with a SQL query. You have to use Python code. This can be done with a SQL query, but none of the above queries will work.
Create a function to increment the salary of employees of an organization by 15%, and for employees with department ID 100 give another 10% hike on the updated salary. Also, write the query to update the salary of all records of the salary column in the employee table using the created function. In PL/SQL
8. Using a subquery in the FROM clause, find the average unit price of in-stock trucks of any make/model in SQL.
QUESTION 2. Write a PL/SQL PROCEDURE that can list the Top-N customers (id and name) and the amounts spent in a specified year together with their rank. The customer rank can be obtained from the function created in the previous question. The total purchase is calculated for each customer using the quantity and price columns. The total purchased is sorted from the highest to the lowest. The procedure will have two input parameters: year (NUMBER) and TOP_N (NUMBER). Hint: • use ONE EXPLICIT CURSOR (corresponding to one select statement). • Us the topN technique (from clause subquery) and rownum to limit the customers. Expected Output for Top 3 customers in 2018 using: execute topSpenders (2018. 3) 3 Gary Jenkins s422.64 Excellent 45 Fred Fontain $359.21 Excellent 16 Ralph Foster $351.64 Excellent Expected Output for Top 10 customers in 2018 using: begin topSpenders(2018,10 end: 3 Gary Jenkins s422.64 Excellent 45 Fred Fontain $359.21 Excellent 16 Ralph Foster $351.64 Excellent 81…
Database Q1: Select the correct SQL statement to provide the user Samer with read only permission on Employees table a) Grant read on Employee where username = Samer; b) Grant read only on Employee to Samer; c) Grant select to Samer on Employee; d) Grant select on Employee to Samer; Q2: Based on the TWO schema structures below, which SQL statement will create a view to show department names where each department has at least 5 employees? Employee (E_ID, E_Name, D_ID, E_Salary) Department (D_ID, D_Name, D_Address) (Select one) a) Create View Dept_Emp as select D_Name, count(E_ID) from Employee, Department where Employee D_ID = Department D_ID Group by D_Name Where count (E_ID) >= 5; b) Create View Dept_Emp as select D_Name, count(E_ID) from Employee D_ID = Department D_ID Group by D_Name Having count (E_ID) >= 5; c) Create View Dept_Emp as select D_Name, count(D_ID) from Employee, Department where Employee D_ID = Department D_ID Group by D_Name Having count (E_ID) >= 5; d)…
Using the Online SQL Editor, answer the following questions with screenshots. Link: https://www.programiz.com/sql/online-compiler/ 1. Show all columns from Order table then add a new column titled “Lowest Price”, where such column flags or labels all amounts less than or equal to 300 as “Items 300 and below”. Answer(screenshot of code & output from SQL Editor) 2. Show last name, first name, and age columns from Customers table then add a new column titled “Age Label”,where such column flags or labels all ages 25 and above as “Tigulang na” and ages below 25 as “Bata pa ah”. Answer(screenshot of code & output from SQL Editor) 3. Provide the order id, item and sum (amount) of all items with a total (sum) amount greater than 400; rename the sum (amount) column as “Total Amount”; then group the result by order_id. Answer(screenshot of code & output from SQL Editor) 4. Show the customer id, last name, and first name of all customers who ordered items with an amount greater than…
Given the table PRODUCT(ProductID, ProductName, ProductClass, ProductWeight, ProductStyle, ProductColor, ProductPrice). Note: ProductWeight and ProductPrice are numeric fields, and the other attributes are character fields, i.e., store text. Write a SQL query to display the product class whose average price is at least 50
i.Write an SQL query to fetch “FIRST_NAME” from Worker table using the alias name as <WORKER_NAME>. ii.Write an SQL query to fetch unique values of DEPARTMENT from Worker table.
Write the SQL code that will change the PROJ_NUM to 14 for employees who were hired before January 1, 1994, and whose job code is at least 501. When you finish Problems 7–15, the EMP_2 table will contain the data shown in Figure P8.15.
4) Display the firstname, lastname of the employees and the customerID that the employee served. Arrange the results by the last name. 5) Write a query to the Name of the Product and the corresponding name of the supplier of that Product for all the products that start with a “P”. 6) Write a SQL query to output the product name and the quantity of the Orders of that product for those orders with more than 70 quantity. (hint: don’t have to use GROUP BY) I need help figuring out the SQL quereys for the questions above using table joining method
Write a PL/SQL using a cursor with Basic loop to update the Price attribute of all flight records inFlight table according to the following conditions: 1- If the FName is Oman Air, change price to 500.2-If the FName is Emirates, change price to 200.3- If the FName is Air India, change price to 250.