Modern Database Management (12th Edition)
12th Edition
ISBN: 9780133544619
Author: Jeffrey A. Hoffer, Ramesh Venkataraman, Heikki Topi
Publisher: PEARSON
expand_more
expand_more
format_list_bulleted
Concept explainers
Videos
Textbook Question
Chapter 6, Problem 6.38PAE
Are based on the class scheduling 3NF relations along with some sample data shown in Figure 6-11. Not shown in this figure are data for an ASSIGNMENT relation, which represents a many-to-many relationship between faculty and sections.
Note that values of the SectionNo column do not repeat across semesters.
Figure 6-11 Class scheduling relations (missing ASSIGNMENT)
Write SQL data definition commands for each of the following queries:
a. How would you add an attribute, Class, to the Student table?
b. How would you remove the Registration table?
c. How would you change the FacuttyName field from 25 characters to 40 characters?
Expert Solution & Answer
Want to see the full answer?
Check out a sample textbook solution
Students have asked these similar questions
Using the Schema - data/statistics Write down the following SQL queries. Assume appropriate data types for each attribute by yourself.
1) Create Bill table.
2)Assume you forgot to set SID as foreign key during creation of Bill table, by default SID is declared as null.Now you have to declare SID as foreign key.
3)Add constraint on names of customer that ensures that names must start with alphabet.
4)Delete constraint that was added in statement 3.
5)Add a new column in any one of the tables.
6)Delete the added column in statement 5
7)Show name of all those customers who have placed order after 23 June 2014 using subquery.
8)Show the data of those customers to who items were sold with SID 1 or 3 or 6
9)Show names of customers and items names of items sold to him
10)Make a vertical partition on customer table in such a way that changes in partitioned portion must be reflected on original table.
11) Update the city of customer as ‘Lahore’ where customer id is 3
12)Delete all those records…
Create both the tables Branch and Supervisor with appropriate attribute names, primary key, foreign key and data type.
Insert the above records into Branch and Supervisor tables.
Display all the records from Branch and Supervisor (Separately)
Display all the details of Name, City and Salary.
Display the total salary for every city in the supervisor table.
Write a PL/SQL program to display BName from the Branch table for D5.
Write a PL/SQL program to display all the details of Supervisor who is getting more than 1250 salary using cursor.
Update city as ‘Suhar’ for Supervisor named Jack.
Write a Procedure to receive a Supervisor SId as Input and display all the related values of that Supervisor. (Also write the calling program.)
Delete all the branch details of operations.
Note that primary keys are in BOLD. Note also that attributes with the same name in different tables are foreign keys. For example, doctorID in the Treatment table is a foreign key that refers to the Doctor table.
Doctor(doctorId:integer, specialtyID:integer, firstName:varchar(20), lastName:varchar(20), salary:numeric(11,2))
Specialty(specialtyID:integer, specialtyName:varchar(20), specialtyCost:numeric(11,2))
Treatment(doctorId:integer, patientSSN:integer, dateTreated:date, costOfTreatment:numeric(11,2))
Patient(patientSSN:integer, firstName:varchar(20), lastName:varchar(20), street:varchar(30), city:varchar(15), state:char(2),zipCode:integer(5))
2.3) Write an SQL statement to increase the cost of all Specialty treatment by 2.5%
2.4) Write a SQL statement to delete all treatment rows that were done before 2015 and cost less than $500.
2.5) Write an SQL statement to select all Specialties where the specialtytName has a null value
Chapter 6 Solutions
Modern Database Management (12th Edition)
Ch. 6 - Prob. 6.1RQCh. 6 - Prob. 6.2RQCh. 6 - Contrast the following terms: a. base table; view...Ch. 6 - Prob. 6.4RQCh. 6 - Prob. 6.5RQCh. 6 - Prob. 6.6RQCh. 6 - List six potential benefits of achieving an SQL...Ch. 6 - Prob. 6.8RQCh. 6 - Distinguish among data definition commands, data...Ch. 6 - Prob. 6.10RQ
Ch. 6 - Prob. 6.11RQCh. 6 - Prob. 6.12RQCh. 6 - Prob. 6.13RQCh. 6 - Drawing on material covered in prior chapters,...Ch. 6 - Explain and provide at least one example Of how to...Ch. 6 - Prob. 6.16RQCh. 6 - What is the difference between COUNT, COUNT...Ch. 6 - What is the evaluation order for the Boolean...Ch. 6 - If an SQL statement includes a GROUP BY clause,...Ch. 6 - Prob. 6.20RQCh. 6 - Prob. 6.21RQCh. 6 - Prob. 6.22RQCh. 6 - Explain why SQL is called a set-oriented language.Ch. 6 - When would the use of the LIKE key word with the...Ch. 6 - Prob. 6.25RQCh. 6 - Prob. 6.26RQCh. 6 - In what order are the clauses of an SQL statement...Ch. 6 - Within which clauses of an SQL statement can a...Ch. 6 - Prob. 6.29RQCh. 6 - Prob. 6.30RQCh. 6 - Prob. 6.31RQCh. 6 - Explain the difference between the WHERE and...Ch. 6 - Prob. 6.33RQCh. 6 - Prob. 6.34PAECh. 6 - Are based on the class scheduling 3NF relations...Ch. 6 - Prob. 6.36PAECh. 6 - Are based on the class scheduling 3NF relations...Ch. 6 - Are based on the class scheduling 3NF relations...Ch. 6 - Are based on the class scheduling 3NF relations...Ch. 6 - Prob. 6.40PAECh. 6 - Are based on the class scheduling 3NF relations...Ch. 6 - Prob. 6.42PAECh. 6 - Prob. 6.43PAECh. 6 - Are based on the class scheduling 3NF relations...Ch. 6 - Prob. 6.45PAECh. 6 - Prob. 6.46PAECh. 6 - Prob. 6.47PAECh. 6 - Prob. 6.48PAECh. 6 - Prob. 6.49PAECh. 6 - Prob. 6.50PAECh. 6 - Prob. 6.51PAECh. 6 - Prob. 6.52PAECh. 6 - Prob. 6.53PAECh. 6 - Modify the Product_T table by adding an attribute...Ch. 6 - Prob. 6.55PAECh. 6 - Add an order to the Order_T table and include a...Ch. 6 - Use the Pine Valley database to answer the...Ch. 6 - Prob. 6.58PAECh. 6 - Prob. 6.59PAECh. 6 - Prob. 6.60PAECh. 6 - Prob. 6.61PAECh. 6 - Prob. 6.62PAECh. 6 - Prob. 6.63PAECh. 6 - Prob. 6.64PAECh. 6 - Prob. 6.65PAECh. 6 - Prob. 6.66PAECh. 6 - Prob. 6.67PAECh. 6 - Prob. 6.68PAECh. 6 - Prob. 6.69PAECh. 6 - Prob. 6.70PAECh. 6 - Prob. 6.71PAECh. 6 - Prob. 6.72PAECh. 6 - Prob. 6.73PAECh. 6 - Prob. 6.74PAECh. 6 - Prob. 6.75PAECh. 6 - Prob. 6.76PAECh. 6 - Prob. 6.77PAECh. 6 - Prob. 6.78PAECh. 6 - Prob. 6.79PAECh. 6 - Prob. 6.80PAECh. 6 - Prob. 6.81PAECh. 6 - Prob. 6.82PAECh. 6 - Prob. 6.83PAECh. 6 - Prob. 6.84PAECh. 6 - Prob. 6.85PAE
Knowledge Booster
Learn more about
Need a deep-dive on the concept behind this application? Look no further. Learn more about this topic, computer-science and related others by exploring similar questions and additional content below.Similar questions
- Write SQL Queries for following set of tables:EMPLOYEE (EmpNo, Name, DoB, Address, Gender, Salary, DNumber)DEPARTMENT (DNumber, Dname, ManagerEmpNo, MnagerStartDate).i) Display the Age of ‘male’ employees.ii) Display all employees in Department named ‘Marketing’.iii) Display the name of highest salary paid ‘female’ employee.iv) Which employee is oldest manger in company?v) Display the name of department of the employee ‘SMITH’..arrow_forwardThe ER diagram of the database is given above. Write the following queries. 1. Write a query to get the First Name and Last Name of all employees from department with ids 30 and 40; Hint: Use the employees table. 2. Write a query to get all first name, last name and emails of employees who make commission greater than 0. Hint: Use the employees table. 3. Write a query to get the Average salary of all employees working as Programmers. Hint: Use the employees table. 4. Write a Query to get the First Name, Last Name and Salary of the employee who is paid the lowest salary in the company. Hint: Use the employees table. 5. Write a Query to get the maximum, minimum and average salary of each department. Hint: Use the employees table.arrow_forwardSubject : Data base management system( MY SQL LIVE) NOTE: write queries for following. 1. Make a table bonus (bonus Id, bonus amount, department no), insert information of multiple departments with department numbers 2, 4, 10, 15, 20 and 25. 2. Delete row when department no is equal to 20, 3. Increment bonus amount when department no is equal to 10 , 4. Delete row where department is between 10 and 20. 5. Delete rows where department number is greater than 20arrow_forward
- Assignment III (5%): Design of the conceptual model (ERD) from the given relational database. (Note: students has to give appropriate labels on relationships and type of relationships between the entities) Use the same Assignment- I schema to perform the PL/SQL programs Q1. The management of the organization has decided to increase the salaries of all employees to 15% for this, write a PL/SQL block to accept the employee number and to update the salary of that employee. Display the output based on the existing records of the employees. (2 marks) Q2. having the employee age above 40 years. (2 marks) Create a PL/SQL a cursor program that will increase the employee salary to 25% who are Q3. where place belongs to Ibri, dunk or Yanul (hint: Project (projectID. projectName, place, start Year, end Year, #DepartmentID) (2 marks) Write a PL/SQL procedure to change the name of the project to "AL Dahariyah Projects"arrow_forwardThree tables are given Student: studentNO, firstName, lastName, studentProgram Professor: professorID, professorProgram, professorName Student_professor: studentNO, professorID, mentor Complete the following requirements: Using the UNION operator, do a full outer join to display the student last name student program, professor name and professor program. Referring to the model created in step 2, determine the PK/FK relationships to build the SQL joining the three Create a query/subquery to display student first name, last name and program. The student program must be same as that in the professor table and the professor program name starts with a ‘C’. This query will use a subquery and not a join.arrow_forwardWrite a query to find the name of the student(s) who has scored maximum mark in Software Engineering. Sort the result based on name. Hint:Use Correlated Sub query or Inline view. Refer the below schema diagram: College Management System(CMS) Department Student department_id number - PK department_name varchar(30) department_block_number number student_id number --> PK student_name varchar(30) address varchar(40) city varchar(30) department_id number -- FK PK- Primary Key FK- Foreign Key Subject Staff staff_id number -> PK staff_name varchar(30) department_id number-> FK subject_id number ->PK subject_name varchar(30) subject_code varchar(10) staf_id number --> FK Mark value number subject_id number-- PK,FK + student_id number--»PKFKarrow_forward
- Use %ROWTYPE for your cursor and use 3 tables in this activity - EMPLOYEES, JOBS, and DEPARTMENTS Write a PL/SQL block to read and display the last name, job title, and department name from departments 50 and 80 only. Display also the number of records returned by your PL/SQL block.arrow_forwardTask 2: The Driver Relationship team wants to arrange workshops and education materials to the drivers. However, the team wants to create clusters of the drivers based on their experience in InstantStay. To collect these detail, you will need to create a SQL function called DRIVER_STATUS to determine the level of the driver as follows: MASTER: more than 4 travels PRO: more than 2 travels ROOKIE: 2 or less travels In addition, run the function to verify it works as expected and send them back the driver levels. Task: Create the DRIVER_STATUS function to create clusters of drivers based on experience. THIS CODE YOU SEE HERE IS WRONG. THERE IS AN ERROR AT LINE 2 DELIMITER < CREATE FUNCTION DRIVER_STATUS(int DRIVERID) RETURNS VARCHAR BEGIN DECLARE //to store number of travels Count int; //to store level Level VARCHAR; //Selects according to passed value SELECT COUNT(*) INTO Level FROM TRAVEL WHERE DRIVER_ID = DRIVERID; //returns level IF( Count > 4) THEN RETURN…arrow_forwardExplain the relationship between EXISTS and correlated subqueries.arrow_forward
- Task 2: The Driver Relationship team wants to arrange workshops and education materials to the drivers. However, the team wants to create clusters of the drivers based on their experience in InstantStay. To collect these detail, you will need to create a SQL function called DRIVER_STATUS to determine the level of the driver as follows: MASTER: more than 4 travels PRO: more than 2 travels ROOKIE: 2 or less travels In addition, run the function to verify it works as expected and send them back the driver levels. SQL Database Test: SELECT DRIVER_ID, DRIVER_STATUS(DRIVER_ID) FROM DRIVERS Create the DRIVER_STATUS function Expected Results: DRIVER_ID DRIVER_STATUS(DRIVER_ID) 2001 MASTER 2002 ROOKIE 2003 PRO 2004 ROOKIE 2005 ROOKIEarrow_forwardTask 2: The Driver Relationship team wants to arrange workshops and education materials to the drivers. However, the team wants to create clusters of the drivers based on their experience in InstantStay. To collect these detail, you will need to create a SQL function called DRIVER_STATUS to determine the level of the driver as follows: MASTER: more than 4 travels PRO: more than 2 travels ROOKIE: 2 or less travels In addition, run the function to verify it works as expected and send them back the driver levels. Task: Create the DRIVER_STATUS function to create clusters of drivers based on experience. (Create the DRIVER_STATUS function)arrow_forwardTask 2: The Driver Relationship team wants to arrange workshops and education materials to the drivers. However, the team wants to create clusters of the drivers based on their experience in InstantStay. To collect these detail, you will need to create a SQL function called DRIVER_STATUS to determine the level of the driver as follows: MASTER: more than 4 travels PRO: more than 2 travels ROOKIE: 2 or less travels In addition, run the function to verify it works as expected and send them back the driver levels. Task: Create the DRIVER_STATUS function to create clusters of drivers based on experience.arrow_forward
arrow_back_ios
SEE MORE QUESTIONS
arrow_forward_ios
Recommended textbooks for you
Database System ConceptsComputer ScienceISBN:9780078022159Author:Abraham Silberschatz Professor, Henry F. Korth, S. SudarshanPublisher:McGraw-Hill Education
Starting Out with Python (4th Edition)Computer ScienceISBN:9780134444321Author:Tony GaddisPublisher:PEARSON
Digital Fundamentals (11th Edition)Computer ScienceISBN:9780132737968Author:Thomas L. FloydPublisher:PEARSON
C How to Program (8th Edition)Computer ScienceISBN:9780133976892Author:Paul J. Deitel, Harvey DeitelPublisher:PEARSON
Database Systems: Design, Implementation, & Manag...Computer ScienceISBN:9781337627900Author:Carlos Coronel, Steven MorrisPublisher:Cengage Learning
Programmable Logic ControllersComputer ScienceISBN:9780073373843Author:Frank D. PetruzellaPublisher:McGraw-Hill Education
Database System Concepts
Computer Science
ISBN:9780078022159
Author:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan
Publisher:McGraw-Hill Education
Starting Out with Python (4th Edition)
Computer Science
ISBN:9780134444321
Author:Tony Gaddis
Publisher:PEARSON
Digital Fundamentals (11th Edition)
Computer Science
ISBN:9780132737968
Author:Thomas L. Floyd
Publisher:PEARSON
C How to Program (8th Edition)
Computer Science
ISBN:9780133976892
Author:Paul J. Deitel, Harvey Deitel
Publisher:PEARSON
Database Systems: Design, Implementation, & Manag...
Computer Science
ISBN:9781337627900
Author:Carlos Coronel, Steven Morris
Publisher:Cengage Learning
Programmable Logic Controllers
Computer Science
ISBN:9780073373843
Author:Frank D. Petruzella
Publisher:McGraw-Hill Education
dml in sql with examples; Author: Education 4u;https://www.youtube.com/watch?v=WvOseanUdk4;License: Standard YouTube License, CC-BY