Computer Networking: A Top-Down Approach (7th Edition)
7th Edition
ISBN: 9780133594140
Author: James Kurose, Keith Ross
Publisher: PEARSON
expand_more
expand_more
format_list_bulleted
Related questions
Question
Make at least 10 sql statements from the table script given (try to make a little complex script) .please make scripts as related to given categories below:(script to make table is given below)
create
create table Books(
Book_id varchar (30)NOT NULL Primary key,
Book_name varchar (80),
Edition_no varchar (20),
category_835757 varchar(20),
price decimal(5,2)
);
INSERT into books
values (1,'Specs','12th','Philosophy',35.11);
INSERT into books
values (2,'Oxford','25th','Dictionary',20.12);
INSERT into books
values (3,'Revolution','9th','Novel',10.15);
INSERT into books
values (4,'Ghost in wires','8th','Crime',13.52);
INSERT into books
values (5,'Burned','4th','Encyclopedia',15.22);
create table Reader(
Userid_757 varchar(50) NOt NULL Primary key,
Last_name varchar(100),
First_name varchar (200),
Phone varchar(100),
Email varchar(200),
Book_id int
);
INsert into reader
values('U513','Mahara','Kapil',647-555-1151,'Phantomconflicted.com',003);
INSERT into reader
values ('U514','Bhatta','Bikas',434-568-1051,'bikas434@yahoo.com',001);
INSERT into Reader
Values ('U515','Ford','Richard',134-091-5122,'ford22.com',002);
Insert into Reader
values ('U516','Bhattarai','Bikram',647-887-2078,'everesl.com',005);
INSERT into reader
values ('U517','Bhoj','Biraj',603-788-1072,'regmi.com',004);
create table Booking (
Booking_id varchar(100) NOT NULL PRIMARY key ,
Booking_date date ,
Return_date date,
User_id varchar(100)
);
Insert into Booking
values ('B011','22-JAN-2021','23-JAN-2021','U513');
Insert into Booking
values ('B012','22-JAN-2021','24-FEB-2021','U514');
Insert into Booking
Values ('B013','24-JAN-2021','28-JAN-2021','U515');
Insert into Booking
values ('B014','25-JAN-2021','27-JAN-2021','U516');
Insert into Booking
Values ('B015', '28-JAN-2021','02-FEB-2021','U517');
create table payment (
Payment_id varchar (100) Not null primary key,
Payment_date date ,
Amount decimal(5,2),
User_id varchar (100)
);
INSERT INTO payment
values ('P115','22-JAN-2021',10.15,'U513');
INSERT into payment
values ('P116','22-JAN-2021',35.11,'U514');
INSERT INTO payment
values ('P117','24-JAN-2021',20.12,'U515');
INSERT INTO payment
values ('P118','25-JAN-2021',15.22,'U516');
INSERT INTO payment
values ('P119','28-JAN-2021',13.52,'U517');
Transcribed Image Text:Category F:
> Single row subqueries
Select customer_id, last_name from customer where salary = (select avg (salary)
from employees)
> Correlated Subqueries
Where clause with correlated sub query
EXISTS Operator
NOT EXISTS Operator
Advance Category:
> Other SQL statements not included above
> Complex SQLS ( Nested Subqueries + subqueries with JOIN etc.)
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 10 images
Knowledge Booster
Similar questions
- Use JOINS (inner, self, left and right, cross and full ) in these tables with sql: create database kapil; create table Books(Book_id varchar (30)NOT NULL Primary key,Book_name varchar (80),Edition_no varchar (20),category_835757 varchar(20),price decimal(5,2));INSERT into booksvalues (1,'Specs','12th','Philosophy',35.11);INSERT into booksvalues (2,'Oxford','25th','Dictionary',20.12);INSERT into booksvalues (3,'Revolution','9th','Novel',10.15);INSERT into booksvalues (4,'Ghost in wires','8th','Crime',13.52);INSERT into booksvalues (5,'Burned','4th','Encyclopedia',15.22); create table Reader(Userid_757 varchar(50) NOt NULL Primary key,Last_name varchar(100),First_name varchar (200),Phone varchar(100),Email varchar(200),Book_id int);INsert into readervalues('U513','Mahara','Kapil',647-555-1151,'Phantomconflicted.com',003);INSERT into readervalues ('U514','Bhatta','Bikas',434-568-1051,'bikas434@yahoo.com',001);INSERT into ReaderValues…arrow_forwardWrite sql code for Use derived tables and joins to create a list of players who have scored more than 200 runs, taken more than 10 wickets, and taken more than two catches. runs is form batting wicketstaken from bowling catches is from fielding table (which is same as other tables0arrow_forwardTask 6: The Car Maintenance team considered that the available maintenance tasks should also have the price information in the database. Alter the MAINTENANCE_TYPES table to include a new column named MAINTENANCE_PRICE of type FLOAT. ANSWER IN MYSQL PLEASEarrow_forward
- Create a table in SQL developer and name it as ‘MyCrush’. The table should consists of only three attributes as follows: Crush_ID which will be the primary key with the number length of .Crush_Name with the maximum character length of 30.Crush_Description with the maximum character length of 10.Insert exactly three records with the above given attributes. Crush_ID values should be 1, 2 and 3 for the three records. Crush_Description should be ‘Present’ for record 1, ‘Past’ for record 2 and ‘Childhood’ for record 3. If you don’t have any crush from your childhood to present, you can insert some random names. Hmm.. Seems Interesting!! It’s time to implement your programming skills using PL/SQL stored procedures and functions. Create another table called ‘MyDetails’ and should contain exactly four attributes as below: Student_ID,Student_Name ,Student_Term ,Student_GPA If you are not a Master’s student, let’s assume you are a student pursuing masters and taking four semesters or terms.…arrow_forwardList all products with their highest unit price, i.e. not discounted.the database isarrow_forwardWrite Create Table SQL statements based on the above ERD. All tables must have primary keys. All tables must have appropriate foreign key constraints. Foreign keys must have the same name, datatype and size of the primary key that they refer to The following columns datatypes and sizes must be used: bid,authid / number(4) title,sname,fname / varchar2(30) sellingprice, payrate / number(6,2) Table Column Type Range author sname & fname Unique book title Not Null book sellingprice Check not negative allocation payrate Check 1 to 79.99arrow_forward
- database: sql Q1. Create the following tables: Carrier Attribute name Type Size Constraint carrierID Varchar2 10 Primary key carrierName Varchar2 100 NOT NULL Phone Varchar2 50 LIKE ‘+966%’ Range Varchar2 30 Value is either ‘International’ or ‘Local’ Review Attribute name Type Size Constraint ReviewID INT Primary key Stars INT NOT NULL Comment Varchar2 70 Name Varchar2 50 Default ’User’ CustomerID INT Foreign key references customers ProductID INTarrow_forwardWrite a select statement for the following: Join all the tables of the lunches database together (Anything table starting with L_). Show all the columns of each table. To do this, modify the select statement in this section and add the three other tables to it. How many rows and columns are in this table?arrow_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_forward
- Write a SQL query to find the customer# of those customers that purchased at least once in July 2020 but NOT in August 2020. In addition to the customer#, show the ISBN and title of the book(s) they purchased in July 2020. You may use only one query. No creating any temporary tables or subqueries. You may use only joins. SUBMIT a second solution in addition to your Level 1.arrow_forwarduse oracle sql developper or Oracle sql developper problem in picture the database CREATE TABLE MEMBERSHIP( MEM_NUM CHAR(3) CONSTRAINT MEMBER_MEM_NUM_PK PRIMARY KEY,MEM_FNAME VARCHAR(30) NOT NULL,MEM_LNAME VARCHAR(30) NOT NULL,MEM_STREET VARCHAR(15),MEM_CITY VARCHAR(10),MEM_STATE CHAR(2),MEM_ZIP CHAR(5),MEM_BALANCE NUMBER (2)); ALTER TABLE MEMBERSHIPMODIFY MEM_STREET VARCHAR(25);CREATE TABLE RENTAL( RENT_NUM CHAR(4) CONSTRAINT RENTAL_RENT_NUM_PK PRIMARY KEY,RENT_DATE DATE,MEM_NUM CHAR(3),CONSTRAINT RENTAL_MEM_NUM_FK FOREIGN KEY (MEM_NUM) REFERENCES MEMBERSHIP); CREATE TABLE PRICE(PRICE_CODE CHAR(1) CONSTRAINT PRICE_PRICE_CODE_PK PRIMARY KEY,PRICE_DESC VARCHAR(20),PRICE_RENTFEE NUMBER (3,1),PRICE_DAILYATFEE NUMBER(3,1)); CREATE TABLE MOVIE(MOVIE_NUM CHAR(4) CONSTRAINT MOVIE_MOVIE_NUM_PK PRIMARY KEY,MOVIE_NAME VARCHAR(30) NOT NULL,MOVIE_YEAR CHAR(4),MOVIE_COST NUMBER(5,2),MOVIE_GENRE VARCHAR(15),PRICE_CODE CHAR(1),CONSTRAINT MOVIE_PRICE_CODE_FK FOREIGN KEY (PRICE_CODE) REFERENCES…arrow_forwardSQL Code for 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 fielders who have played 7 times as many matches as the wicketkeeper(s) in 5).arrow_forward
arrow_back_ios
SEE MORE QUESTIONS
arrow_forward_ios
Recommended textbooks for you
- Computer Networking: A Top-Down Approach (7th Edi...Computer EngineeringISBN:9780133594140Author:James Kurose, Keith RossPublisher:PEARSON
Computer Organization and Design MIPS Edition, Fi...Computer EngineeringISBN:9780124077263Author:David A. Patterson, John L. HennessyPublisher:Elsevier Science
Network+ Guide to Networks (MindTap Course List)Computer EngineeringISBN:9781337569330Author:Jill West, Tamara Dean, Jean AndrewsPublisher:Cengage Learning 
Concepts of Database ManagementComputer EngineeringISBN:9781337093422Author:Joy L. Starks, Philip J. Pratt, Mary Z. LastPublisher:Cengage Learning
Prelude to ProgrammingComputer EngineeringISBN:9780133750423Author:VENIT, StewartPublisher:Pearson Education
Sc Business Data Communications and Networking, T...Computer EngineeringISBN:9781119368830Author:FITZGERALDPublisher:WILEY
Computer Networking: A Top-Down Approach (7th Edi...
Computer Engineering
ISBN:9780133594140
Author:James Kurose, Keith Ross
Publisher:PEARSON
Computer Organization and Design MIPS Edition, Fi...
Computer Engineering
ISBN:9780124077263
Author:David A. Patterson, John L. Hennessy
Publisher:Elsevier Science
Network+ Guide to Networks (MindTap Course List)
Computer Engineering
ISBN:9781337569330
Author:Jill West, Tamara Dean, Jean Andrews
Publisher:Cengage Learning
Concepts of Database Management
Computer Engineering
ISBN:9781337093422
Author:Joy L. Starks, Philip J. Pratt, Mary Z. Last
Publisher:Cengage Learning
Prelude to Programming
Computer Engineering
ISBN:9780133750423
Author:VENIT, Stewart
Publisher:Pearson Education
Sc Business Data Communications and Networking, T...
Computer Engineering
ISBN:9781119368830
Author:FITZGERALD
Publisher:WILEY