Database System Concepts
7th Edition
ISBN: 9780078022159
Author: Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan
Publisher: McGraw-Hill Education
expand_more
expand_more
format_list_bulleted
Related questions
Concept explainers
Question
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 of Items table whose price is between 15 and 240
13)Insert a record in any one of the tables
14)Change data type of name of customer to varchar(20)
15)Display details of all those items that are not sold yet
Transcribed Image Text:Items
Sales
ID
_Name
Price
Supplier
SID
S Date
CID
Ketchup
1
2012-5-23
1.
50
Block Factory
2012-5-24
2
Unique
Manufacturer
S&S
2
Salt
40
4
2016-4-23
3
2016-2-12
4
Soap
30
6
2016-1-10
International
4
Toothpaste
33
Brothers
Enterprise
General
5
Sugar
20
Bill
Traders
6.
Detergent
34
Alibaba
SID
ID
Express
2
1
3
Customer
4
CID
Name
2
1
City
Lahore
Peshawar
Sahiwal
Peshawar
2
4
Aqib
Suleman
Nadeem
2
2
2
4
4
Sarim
Sohail
Peshawar
4
3
6
Nadir
Okara
4
2
Nasir
Peshawar
4
4
Kamal
Lahore
Expert Solution
This question has been solved!
Explore an expertly crafted, step-by-step solution for a thorough understanding of key concepts.
Step by stepSolved in 2 steps with 6 images
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
- Using the relations in the appendix, write the following queries in SQL:i. Display a list of all instructors, showing each instructor’s ID and the number of sections taught. Your query should use an outer join, and should not use sub-queries.ii. Write the same query as in part a, but using a scalar subquery and not using outer join.iii. Display the list of all course sections offered in spring 2018, along with the ID and name of each instructor teaching the section. If a section has more than one instructor, that section should appear as many times in the result as it has instructors. If a section does not have any instructor, it should still appear in the result with the instructor name set to “—”.iv. Display the list of all departments, with the total number of instructors in each department, without using sub-queries. Make sure to show departments that have no instructors, and list those departments with an instructor count of zero.arrow_forwardUsing the university schema, write an SQL query to find the ID and title of each course in Comp. Sci. that has had at least one section with afternoon hours (i.e., ends at or after 12:00). (You should eliminate duplicates if any.)arrow_forwardExecute a SQL statement to add a check constraint to the field mode_of_payment with values ('D', 'R' or 'C") in order_dbarrow_forward
- 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)…arrow_forwardI NEED HELP WITH THIS QUESTION FOR MY DATABASE MANAGEMENT CLASS FOR SQL DEVELOPER In reporting total_sale by each REP including those who made no sale, which Table joins should be left join. And how to show zero in your report instead of null for reps with no sale.arrow_forwardWrite a query in SQL to display all the data of customers, in Table customers, that live in New York or California SELECT city FROM customers WHERE city = 'New York' OR city = 'California' SELECT * FROM customers WHERE city = 'New York' AND city = 'California' SELECT * FROM customers WHERE city = 'New York' OR city = 'California' SELECT ALL FROM customers WHERE city = 'New York' OR city = 'California' %3Darrow_forward
- 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 50arrow_forwardWrite 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.arrow_forwardWrite a SQL query to display the patron ID, first and last name, and patron type for all patrons whose last name begins with the letter “C”, sorted by patron ID (Figure P7.73).arrow_forward
- write sql statement to modify the position table in order to define a constraint to ensure that the column position receive only values salad rep or manager course heroarrow_forwardWrite queries in SQL to answer each of the following questions: 1. Find all students in Comp. Sci. dept 2. Find all students with total credits > 100 3. Find all students who took course in Spring 2010 (Remove duplicates please) 4. Find all courses taken by the student whose ID is 76543 and also find his name create table student (ID varchar(5), name varchar(20) not null, dept_name varchar(20), tot_cred numeric(3,0), primary key (ID) ); create table takes (ID varchar(5), course_id varchar(8), sec_id varchar(8), semester varchar(6), year numeric(4,0), grade varchar(2), primary key (ID, course_id, sec_id, semester, year) ); insert into student values ('00128', 'Zhang', 'Comp. Sci.', '102'); insert into student values ('12345', 'Shankar', 'Comp. Sci.', '32'); insert into student values ('19991', 'Brandt', 'History', '80'); insert into student values ('23121', 'Chavez', 'Finance', '110'); insert into student values ('44553', 'Peltier', 'Physics', '56'); insert into student values…arrow_forwardBased on the following authorication commands (ordered from 1 to 4), which of the following privileges is true, assuming that Ali is the owner of the database? Order By Ali 1 2 3 4 Select one: Omar Ali Ali Command Grant Select on Employee to Omar with grant option; Grant Select on Employee to Samer with grant option; Grant Update on Employee to Omar with grant option; Revoke Select on Employee From Omar Cascade; a) Ali and Omar have Update privilege on employee Ali has select privilege on employee b) Ali and Omar have Update privilege on employee Omar and Samer have select privilege on employee elect c) Ali and Omar have Update privilege on employee Ali and Omar have select privilege on employee d) Ali and Omar have Update privilege on employee Ali, Omar and Samer have select privilege on employeearrow_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