Using the table Student and Course already available in the given workspace, execute the following queries. 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.

Using the table Student and Course already available in the given workspace, execute the following queries. 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

In 5 minutes.
From the Salesm an and Customer tables below, write SQL statements to do the following: a) List the sales man id, rating and names of those sales men who getrating from 3 to 5, sorted DESCENDING by rating. b) List all the customers' informat ion whose name starts with 'F' and the fourth character is T, rests may be any charac ter. c) List the name andcountry of those customers and sales men who belongs to the same country. Salesman table: Salesman Salesman_ rating _id country name 2001 Mc Lyon 3 Italy 2005 James hood 2 Canada 2045 Paul Hen USA 2010 Nail Alex 4 France 2060 Salm E 5 KSA Cust omer table: Cust_id Cust_nam grade country Salesman e _id 1 Nick 100 France 2010 Rimando 2 Brad Davis 400 Canada 2005 3 Fabian 200 Japan 2045 Johnson 4 UK Jozy Altidor 100 2010 5 Brad Guzan 500 Canada 2005
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.
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
Write all the sql code (including create, insert and select)
Problem: Having created the table structure in Problem 1, write the SQL code to enter the first two rows for the table shown in Figure P8.2. Each row should be inserted individually, without using a subquery. Insert the rows in the order that they are listed in the figure.I gor an error: RROR 1062 (23000) at line 1: Duplicate entry '101' for key 'PRIMARY' when I used this script below, I got an error. see image.ERROR 1062 (23000) at line 1: Duplicate entry '101' for key 'PRIMARY' INSERT INTO EMP_1 VALUES ('101','News','John','G','2000-11-08','502');INSERT INTO EMP_1 VALUES ('102','Senior','David','H','1989-07-12','501');
Write an SQL query that will show all data from the Customers table andOrder By State.
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
Write a SQL query, which shows a list of all employees last name, their department names and their department city where that department is located from the employees table. If an employee does not belong to a department, then their department number should show as “N/A” and their department city should show up as “No City info Found”. MAKE SURE you have all the employees displayed including the employees who do not have a department.