### SQL Query Instances: An Educational ExerciseThe following SQL queries utilize tables that represent instances of `Reserves`, `Boats`, and `Sailors`. #### Table 1: R1 of ReservesAn instance of the `Reserves` relation:| sid | bid | day ||-----|-----|-----------|| 22 | 101 | 10/10/96 || 58 | 103 | 11/12/96 || 22 | 102 | 10/10/96 || 31 | 101 | 10/10/98 || 31 | 102 | 10/22/98 || 22 | 104 | 10/10/99 || 22 | 102 | 10/11/98 || 22 | 101 | 10/7/99 || 64 | 104 | 10/9/98 || 58 | 102 | 09/05/98 || 64 | 102 | 09/08/98 || 58 | 102 | 09/08/98 || 22 | 102 | 10/11/98 || 31 | 102 | 11/09/98 || 64 | 102 | 09/05/98 || 64 | 103 | 09/08/98 |#### Table 2: B1 of BoatsAn instance of the `Boats` relation:| bid | bname | bcolor ||-----|----------|--------|| 101 | Interlake| blue || 102 | Interlake| red || 103 | Clipper | green || 104 | Marine | red |#### Table 3: S1 of SailorsAn instance of the `Sailors` relation:| sid | sname | rating | age ||-----|-------|--------|-----|| 22 | yuppy | 3 | 35 || 31 | lubber| 8 | 55 || 64 | guppy | 5 | 35 || 58 | rust

Given database consisting data of reserves , boats and sailors. To solve the above given questions…

The following SQL queries use the instances of R1 of "Reserves" the instances of B1 of "Boats", and the instances of S1 of "Sailors" relation. An instance R1 of Reserves bid 101 day 10/10/96 11/12/96 103 10/11/96 12/01/96 12/05/96 10/10/09 10/10/98 10/07/98 RO 22 58 22 31 31 22 22 31 64 64 74 103 104 103 102 104 102 101 102 103 11/10/98 09/08/98 09/05/98 09/08/98 An instance B1 of Boats bid 101 102 103 bname Interlake Interlake Clipper Marine 104 bcolor blue red green red An instance S2 of Sailors Sid 28 31 21 44 58 256 22 22 29 29 32 m 64 21 74 85 95 71 sname yuppy Tubber guppy rusty Dustin Brutus Andy Horatio Horatio Zorba Art Bob Amy rating 81 5 10 711 L & |10|| 21 3 115 age 35 155 135 135 45 $3 25 35 18 25 635 46 1.If we wish to list out only the names and ratings of the sailors, we can write this in SQL statement as 2.Find out only the names and ages of sailors. 3.To find out the names and ratings of sailors with a rating above 8, we can combine two of the preceding queries as: 4.Find the names of sailors who are older than 40.

The following SQL queries use the instances of R1 of "Reserves" the instances of B1 of "Boats", and the instances of S1 of "Sailors" relation. An instance R1 of Reserves bid 101 day 10/10/96 11/12/96 103 10/11/96 12/01/96 12/05/96 10/10/09 10/10/98 10/07/98 RO 22 58 22 31 31 22 22 31 64 64 74 103 104 103 102 104 102 101 102 103 11/10/98 09/08/98 09/05/98 09/08/98 An instance B1 of Boats bid 101 102 103 bname Interlake Interlake Clipper Marine 104 bcolor blue red green red An instance S2 of Sailors Sid 28 31 21 44 58 256 22 22 29 29 32 m 64 21 74 85 95 71 sname yuppy Tubber guppy rusty Dustin Brutus Andy Horatio Horatio Zorba Art Bob Amy rating 81 5 10 711 L & |10|| 21 3 115 age 35 155 135 135 45 $3 25 35 18 25 635 46 1.If we wish to list out only the names and ratings of the sailors, we can write this in SQL statement as 2.Find out only the names and ages of sailors. 3.To find out the names and ratings of sailors with a rating above 8, we can combine two of the preceding queries as: 4.Find the names of sailors who are older than 40.

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

True or False below: Given two relations named A and B, if we q-JOIN A and B, the maximum number of attributes in the result relation could be (the number of attributes of A) times(*) the number of attributes of B). * in an SQL SELECT statement means “all the tuples”. In relational algebra, Cartesian Product (X) can be implemented using the q-JOIN operator. Cartesian Product ISA q-JOIN where q-condition always evaluates to true. SQL is based on Predicate Calculus. A query in SQL always specifies what should be included in the query result.
PLS HELP ME : Write expressions of relational algebra to answer the following queries Find MaMH and TenMH of all courses having SOTIET greater than 45. Find MaSV, HoSV, TenSV of all students of the department Hệ thống thông tin. (denoted department name is TenMH) Find MaKH, TenKH of all departments having no students. (denotation of departments is MaKH, , student code is denoted by MaSV) 4. Find MaMH, TenMH of all courses learned by both Lê Kim Lan and Trần Minh Chánh. (the symbol of the courses is MAKH) 5. Find the MaSV, HoSV, TenSV of student having the highest scholarship. (the symbol for the scholarship is HOCBONG) 6. Find the MaSV, HoSV, TenSV of all students participating in at least two different courses. 7. Find the MaSV, HoSV, TenSV of all students participating in one course many times.
Structured Query Language (SQL) is a language widely used in industry to create, update and query data in relational databases. You are required to write the SQL code. Q.6.1 The below sample data in the third normal form was provided by a database designer. Answer the below questions using this data. Table: LawFirm Primary key: LawFirmID (auto number) All fields are mandatory LawFirmID Name Yearlncorporated 1 Ze Boss Attorneys 2011 2 Lawyers for the Win 2018 3 Court Rulez 2019 Table: Lawyer Primary key: LawyerlD Foreign key: LawFirmID LawyerID LawFirmlD Surname Name Age Bianchi Anna 45 7 2 Modise Dikeledi 57 8 2 Ferreira Piet 34 3 Nkosi Jabulani 28 Q.6.1.1 Write a SQL statement to create the table Lawyer. Hint: The sample data should give you an indication of the data types you should use. Q.6.1.2 Write a SQL statement that will calculate the average age of all the lawyers that are at least 40 years old. Q.6.1.3 Write a SQL statement to insert the below row into table LawFirm.
What happens if we don’t specify the WHERE clause in an SQL DELETE FROM statement? a) statement compilation causes a “Syntax error”, and the statement is not executed. b) all the tuples of the relaton are deleted. c) none of the tuples of the relation is deleted. d) all the tuples not satisfying the condition are deleted. e) none of the above. Which one(s) of the following is(are) procedural? a) Relational Algebra b) Relational Calculus c) Tuple Relational Calculus d) Domain Relational Calculus e) SQL f) (a) and (e) e) (b) and (e).
SQL QUERIES Employees(EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE,JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID,DEPARTMENT_NAME)Department(DEPARTMENT_ID, DEPARTMENT_NAME, MANAGER_ID, LOCATION_ID)Location(LOCATION_ID,STREET_ADDRESS,POSTAL_CODE,CITY,STATE_PROVINCE,COUNTRY_ID) Write a query in SQL to display the DEPARTMENT_NAME of all those Departments whoseDEPARTMENT_ID is greater than the DEPARTMENT_ID of Physics department. Write a Query in SQL to show the FIRST_NAME of all those employees whose salary is lessthan the salary of all those employees whose MANAGER_ID is 101 or 102.
WorksOn Database: Employee (EmployeeNumber, EmployeeName, BirthDate, Title, Salary, DepartmentNumber) Project (ProjectNumber, ProjectName, Budget, DepartmentNumber) Department (DepartmentNumber, DepartmentName, ManagerNumber) WorksOn (EmployeeNumber, ProjectNumber, Responsibility, Hours) Questions: 1) Write an SQL query that returns the project number and name for projects with a budget no greater than $1,050,000. 2) Write an SQL query that returns all works on records where hours worked is less than 5 and the responsibility is 'Manager'. 3) Write an SQL query that returns the employees (number and name only) who have a title of 'EE' or 'SA' and make more than $50,000. 4) Write an SQL query that returns the employees (name and salary only) in department 'D1' ordered by decreasing salary. 5) Write an SQL query that returns the departments (all fields) ordered by descending department name.
Given the tables create table T (A int primary key, B int); create table U (C int primary key, A int, foreign key(A) references T(A) ); Table T contains 100 rows and table U contains 200 rows. A common mistake made by students is to do a join and assuming the database will supply the join predicate. A student does the query SELECT T.A, T.B, U.C FROM T, U; The student does not get an error message but gets a large result set. How many rows are in the result set?
Consider the following ERD; consider additional attributes if needed Write relational algebra expressions on following: 1) Find the test details that has performed on patient 'Arisha' 2) Find the total test amount of patient 'Nishant'. a) Find the doctors details with dss='d01' with list of his patients insurance date-admitted name date-checked-out ss# patients test-log Dr-Patient test_id performed by doctors test dss# specialization name test_name date time result
Instructor(Instructor_Id, Instructor_Name, specialization, Address,#Department_Id)Department(Department_Id, Department_Name)Course(Course_code, Course_Title,Credit_hours,Contact_hours,#Department_Id)Teaching(#Instructor_Id, #Course_code)a) Express in SQL the following queries:i. Change to 6 the number of contact hours of the course which has thecode “ITDR2104”.ii. Add a new column named instructorLoad of type Number(2) to thetable instructor.iii. List the details of all the courses having a code that starts with“ITDR”. The output should be sorted in descending order by credithours.iv. Find the number of courses taught by the instructor whose name is“Ahmed”.v. List the names of all departments along with the number of coursesoffered by each department.vi. Find the titles of courses, which are taught by the instructor who isnamed “Ahmed”.b) Create a view named instructor_of_DBCourse to contain the details(Instructor_Name, specialization, Address, Department_Id) ofinstructors who teach…
SQL Database Write PL/SQL or T-SQL procedures to accomplish the following tasks: Obtain the first name and last name, separated by a space, of the guide whose number currently is stored in I_GUIDE_NUM. Place these values in the variables I_FIRST_NAME and I_LAST_NAME. Output the contents of I_GUIDE_NUM, I_FIRST_NAME, and I_ LAST_NAME. Obtain the number of persons, customer number, and customer last name for every reservation whose ID currently is stored in I_RESERVATION_ID. Place these values in the variables I_NUM_PERSONS, I_CUSTOMER_NUM, and I_LAST_NAME, respectively. Output the contents of I_NUM_PERSONS, I_CUSTOMER_NUM, and I_LAST_NAME. Add a row to the GUIDE table. Change the last name of the guide whose number is stored in I_GUIDE_NUM to the value currently found in I_LAST_NAME. Delete the guide whose number is stored in I_GUIDE_NUM