DATABASE SYSTEMS
13th Edition
ISBN: 9780357095607
Author: Coronel
Publisher: CENGAGE L
expand_more
expand_more
format_list_bulleted
Concept explainers
Videos
Textbook Question
thumb_up100%
Chapter 8, Problem 6P
Write the SQL code to delete the row for William Smithfield, who was hired on June 22, 2004, and whose job code is 500. (Hint: Use logical operators to include all of the information given in this problem. Remember, if you are using MySQL, you will have to first disable “safe mode.”)
Expert Solution & Answer
Trending nowThis is a popular solution!
Students have asked these similar questions
Using the Online SQL Editor, answer the following questions with screenshots.
Link: https://www.programiz.com/sql/online-compiler/
1) Show the columns item and amount from Orders table which starts with the letter “M” but doesn’t end with the letter “r”;
2) Show the first name and last name columns from the Customers table which either starts with the letter “J” or ends with the letter “e”. Answer(screenshot of code & output from SQL Editor)
Use FOR loop for your cursor.
Write a PL/SQL block to read and display the department name and number of employees per department. Accept input from
the user to get the number of employees he wants to see in the output.
Display also the number of records returned by your PL/SQL block.
THIS MODULE IS ABOUT SUBQUERIES, SO YOU MUST USE SUBQUERIES INSTEAD OF TABLE JOINS
(a) From which two fields in which table can you calculate the enrollment of a section?(b) From which table can you get both course and section numbers?(c) Which field in table SECTION uniquely identifies a section, section id or section no?(d) Write a SQL statement to show sections and their enrollment.(e) Write a SQL statement to show sections with enrollment greater than five. Display both course andsection numbers
Chapter 8 Solutions
DATABASE SYSTEMS
Ch. 8 - Prob. 1RQCh. 8 - Explain why it might be more appropriate to...Ch. 8 - What is the difference between a column constraint...Ch. 8 - What are referential constraint actions?Ch. 8 - What is the purpose of a CHECK constraint?Ch. 8 - Explain when an ALTER TABLE command might be...Ch. 8 - What is the difference between an INSERT command...Ch. 8 - What is the difference between using a subquery...Ch. 8 - What is the difference between a view and a...Ch. 8 - Prob. 10RQ
Ch. 8 - Prob. 11RQCh. 8 - Prob. 12RQCh. 8 - Write the SQL code that will create only the table...Ch. 8 - Having created the table structure in Problem 1,...Ch. 8 - Prob. 3PCh. 8 - Write the SQL code that will save the changes made...Ch. 8 - Write the SQL code to change the job code to 501...Ch. 8 - Write the SQL code to delete the row for William...Ch. 8 - Write the SQL code to create a copy of EMP_1,...Ch. 8 - Using the EMP_2 table, write the SQL code that...Ch. 8 - Using the EMP_2 table, write the SQL code to...Ch. 8 - Prob. 10PCh. 8 - Prob. 11PCh. 8 - Prob. 12PCh. 8 - Prob. 13PCh. 8 - Prob. 14PCh. 8 - Prob. 15PCh. 8 - Create the CUSTOMER table structure illustrated in...Ch. 8 - Create the INVOICE table structure illustrated in...Ch. 8 - Prob. 18PCh. 8 - Prob. 19PCh. 8 - Create an Oracle sequence named CUST_NUM_SEQ to...Ch. 8 - Create an Oracle sequence named INV_NUM_SEQ to...Ch. 8 - Prob. 22PCh. 8 - Modify the CUSTOMER table to include the customers...Ch. 8 - Prob. 24PCh. 8 - Prob. 25PCh. 8 - Create a trigger named trg_updatecustbalance to...Ch. 8 - Prob. 27PCh. 8 - Prob. 28PCh. 8 - Write a trigger to update the customer balance...Ch. 8 - Prob. 30PCh. 8 - Create a trigger named trg_line_total to write the...Ch. 8 - Create a trigger named trg_line_prod that...Ch. 8 - Create a stored procedure named prc_inv_amounts to...Ch. 8 - Create a procedure named prc_cus_balance_update...Ch. 8 - Modify the MODEL table to add the attribute and...Ch. 8 - Prob. 36PCh. 8 - Modify the CHARTER table to add the attributes...Ch. 8 - Write the sequence of commands required to update...Ch. 8 - Write the sequence of commands required to update...Ch. 8 - Write the command required to update the...Ch. 8 - Write the command required to update the...Ch. 8 - Write the command required to update the...Ch. 8 - Prob. 43PCh. 8 - Create a trigger named trg_char_hours that...Ch. 8 - Create a trigger named trg_pic_hours that...Ch. 8 - Create a trigger named trg_cust_balance that...Ch. 8 - Write the SQL code to create the table structures...Ch. 8 - The following tables provide a very small portion...Ch. 8 - For Questions 49-63, use the tables that were...Ch. 8 - Prob. 50PCh. 8 - Write a single SQL command to increase all price...Ch. 8 - Alter the DETAILRENTAL table to include a derived...Ch. 8 - Update the DETAILRENTAL table to set the values in...Ch. 8 - Alter the VIDEO table to include an attribute...Ch. 8 - Update the VID_STATUS attribute of the VIDEO table...Ch. 8 - Alter the PRICE table to include an attribute...Ch. 8 - Prob. 57PCh. 8 - Prob. 60P
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 the SQL code to delete the row for William Smithfield, who was hired on June 22, 2004, and whose job code is 500. (Hint: Use logical operators to include all of the information given in this problem.)arrow_forwardInstructions Write SQL queries to solve the following problems. We will be using the “university” database. All queries should involve only the instructor table. After you are done, save your work as .sql file and turn in the .sql file into Moodle for grading. Write a SQL statement to display all columns using the SQL asterisk (*) wild-card character. Write a SQL statement to display ID and name of the instructors (ID appears first). Write a SQL statement to display department names. Write a SQL statement to display unique department names. (expect 7 rows returned) Write a SQL statement to display all info about instructors having a salary greater than 75000. (expect 6 rows returned) Write a SQL statement to display department name, instructor name, and salary for instructors that have a salary less than 70000. Sort the results in ascending order by department name. (expect 4 rows returned) Write a SQL statement to display name, department name, and salary for instructors that have a…arrow_forwardWrite the SQL code that will produce the same information that was shown in Problem 2, but sorted by the employee’s last name. The results of running that query are shown in Figure P7.3.arrow_forward
- Using the Online SQL Editor, answer the following questions with screenshots. Link: https://www.programiz.com/sql/online-compiler/ 1. Provide the list of complete names (first & last name) and country of customers whose age is recurring in the list more than once; select and show the count column as Age and rename such column as “22_Years_Old”. Answer(screenshot of code & output from SQL Editor). 2. Show all the columns of the particular item from the order table with the highest price (amount) from the list. Answer(screenshot of code & output from SQL Editor). 3. (1) Show items and amounts from the order table; compute the average amount; rename the column as “Average_price”; then group the columns by item. (2) Compute the sum of all amounts pertaining to keyboard from the order table; rename the column as “Total_price_keyboard”. Answer(screenshot of code & output from SQL Editor).arrow_forwardUse FOR loop for your cursor. Write a PL/SQL block to read and display the job_id and number of employees per job_id. Accept input from the user to get the number of employees he wants to see in the output. Display also the number of records returned by your PL/SQL block.arrow_forwardProblem: 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');arrow_forward
- Using the Online SQL Editor, answer the following questions with screenshots. Link: https://www.programiz.com/sql/online-compiler/ Question 1: Show (in one table) customer id and first name from Customers table together with shipping id and status from Shippings table while assigning them, within the SQL code, the aliases CT and ST, respectively. *CT for Customers table and corresponding columns*ST for Shippings table and corresponding columns Question 2: Show customer id, item, and amount from Orders table together with the last name and age from Customers table whose age is 25 and above. Question 3: Create a new table named Suppliers, with the following columns: Supplier id, first name, last name, age, and country. Limit first name and last name to 40 characters, country to 15 characters, and age to 3 characters.arrow_forwardNote: PL/SQL can be executed in SQL*Plus or SQL Developer or Oracle Live SQL. 1. Write an anonymous block to retrieve the doctor’s ID and name which in charge of certain patient. Allow the user to enter the patient’s ID. 2. Write an anonymous PL/SQL block that will insert a new doctor into the DOCTOR table. Verify that insert has been done successfully by issuing a select * from doctor. 3. Write an anonymous PL/SQL block that will update the salary of all doctors in the Pediatrics area by 1000 (Note: Current salary + 1000). Verify that the salary has been updated by issuing a select * from doctor where area = ‘Pediatrics’. You may have to run the select statement twice to check the data before and after the update. 4. Write an anonymous PL/SQL block that will delete all patients for doctors that works in the Family Practice area. Verify that patients have been deleted accordingly. Then do a select * from patients where doc_id = (select doc_id from doctor where area = ‘Family Practice’.…arrow_forwardWrite SQL code for (see image for the table structure): Create a stored procedure “changeCreditLimit” to change the credit limit for a given customer. If the customer’s total payment amount (note: payment amount is in the table “payments”) is not smaller than a given amount, then add 2000 to the customer’s credit limit (note: credit limit is in the table “customers”).arrow_forward
- This question should be answered in PL/SQL only! Create a procedure that takes instructor_ID as a parameter. Name it INSTR_SECTIONS. The procedure displays instructor first and last names, and also a list of sections that the instructor is scheduled to teach. Only one instructor per procedure call. You give/pass to a procedure instructor_ID as a parameter, the procedure prints INFO related only to that instructor.arrow_forwardWrite SQL code for To Extend the code in ( Perform a LEFT OUTER JOIN between the tables Wicketkeeping and Batting. You may select any column(s). Look at the result and state how many wicketkeepers have not played as batsmen (no need to write code for this part) To show the name of the wicketkeeper(s) who have not played as a batsman. (syntax to check a NULL value in a column is: WHERE ColumnName IS NULL ) ?arrow_forwardYou have the following tables: MANAGERS (MANAGER_ID, LAST_NAME, FIRST_NAME, DEPARTMENT) ACCOUNTS (ACCOUNT_NUMBER, MANAGER_ID, CUST_ID, BALANCE) CUSTOMERS (CUST_ID, LAST_NAME, FIRST_NAME) Write a SQL statement that lists account number, balance, MANAGER’s last name, CUSTOMER ID, and CUSTOMER’s last name for every account in the ACCOUNTS table.arrow_forward
arrow_back_ios
SEE MORE QUESTIONS
arrow_forward_ios
Recommended textbooks for you
Database Systems: Design, Implementation, & Manag...Computer ScienceISBN:9781305627482Author:Carlos Coronel, Steven MorrisPublisher:Cengage Learning
Database Systems: Design, Implementation, & Manag...Computer ScienceISBN:9781285196145Author:Steven, Steven Morris, Carlos Coronel, Carlos, Coronel, Carlos; Morris, Carlos Coronel and Steven Morris, Carlos Coronel; Steven Morris, Steven Morris; Carlos CoronelPublisher:Cengage Learning
A Guide to SQLComputer ScienceISBN:9781111527273Author:Philip J. PrattPublisher:Course Technology Ptr
Database Systems: Design, Implementation, & Manag...
Computer Science
ISBN:9781305627482
Author:Carlos Coronel, Steven Morris
Publisher:Cengage Learning
Database Systems: Design, Implementation, & Manag...
Computer Science
ISBN:9781285196145
Author:Steven, Steven Morris, Carlos Coronel, Carlos, Coronel, Carlos; Morris, Carlos Coronel and Steven Morris, Carlos Coronel; Steven Morris, Steven Morris; Carlos Coronel
Publisher:Cengage Learning
A Guide to SQL
Computer Science
ISBN:9781111527273
Author:Philip J. Pratt
Publisher:Course Technology Ptr
dml in sql with examples; Author: Education 4u;https://www.youtube.com/watch?v=WvOseanUdk4;License: Standard YouTube License, CC-BY