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
Write 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 tables0
Transcribed Image Text:CREATE TABLE Bowling (
Player CHAR(25),
CareerSpan VARCHAR ( 20),
Matches INT,
Innings INT,
Overs INT,
Maidenovers INT,
RunsGiven INT,
wicketsTaken INT,
BestBowlingFigure VARCHAR (8),
Average NUMERIC,
Economy NUMERIC,
StrikeRate NUMERIC,
FoursGiven INT,
SixesGiven INT
);
INSERT INT0 Bowling
VALUES ('DW Steyn',
INSERT INT0 Bowling
VALUES ('Imran Tahir',
INSERT INTO Bowling
VALUES ('M Morkel'
INSERT INTO Bowling
VALUES ('WD Parnell',
INSERT INTO Bowling
VALUES ('J Botha',
INSERT INT0 Bowling
VALUES ('KJ Abbott',
INSERT INTO Bowling
VALUES ('JA Morkel',
INSERT INT0 Bowling
VALUES ('RJ Peterson',
INSERT INTO Bowling
VALUES ('D Wiese',
INSERT INT0 Bowling
VALUES ('K Rabada',
INSERT INTO Bowling
VALUES ('CH Morris',
INSERT INTO Bowling
VALUES ('JP Duminy',
'2007-2016',
42,
42,
150.1, 2,
1009,
58,
'4/9',
17.39,
6.71,
15.5,
2,
0);
'2013-2017',
33,
33,
122.5,
0,
815,
55,
'5/24', 14.81,
6.63,
13.4,
2,
1);
'2007-2017',
41,
41,
146.4,
3,
1097,
46,
'4/17', 23.84,
7.47,
19.1,
2,
0);
'2009-2017',
40,
39,
124.5,
3,
1038,
41,
'4/13', 25.31,
8.31,
18.2,
1,
0);
'2006- 2012 ',
40,
39,
129.0,
1,
823,
37,
'3/16', 22.24,
6.37,
20.9,
0,
0);
'2013-2016',
21,
21,
72.4,
0,
579,
26,
'3/20', 22.26,
7.96,
16.7,
0,
0);
'2005 - 2015',
50,
46,
107.5,
2,
864,
26,
'3/12', 33.23,
8.01,
24.8,
0,
0);
'2006-2014',
21,
19,
59.5,
1,
451,
24,
'3/28', 18.79,
7.53,
14.9,
0,
0);
'2013-2016 ',
20,
20,
65.2,
0,
497,
24,
'5/23', 20.70,
7.60,
16.3,
0,
1);
'2014-2018',
17,
17,
64.2,
0,
522,
24,
'3/30', 21.75,
8.11,
16.0,
0,
0);
'2012-2018',
17,
17,
63.0,
2,
529,
23,
'4/27', 23.00,
8.39,
16.4,
1,
0);
'2007-2018',
77,
40,
76.1,
0,
581,
21,
'3/18', 27.66, 7.62,
21.7,
0,
0);
Transcribed Image Text:Balling
REATE TABLE Batting (
Player CHAR(25),
CarrerSpan VARCHAR(20),
Matches INT,
Innings INT,
Playing INT,
Runs INT,
Highestscore CHAR(10),
AverageScore NUMERIC,
BallsFaced INT,
strikeRate NUMERIC,
Hundreds INT,
Fifties INT,
Zeros INT,
Fours INT,
Sixs INT
NSERT INTO Batting
ALUES ('JP Duminy', '2007-2018',
NSERT INTO Batting
ALUES ('AB de villiers', '2006-2017',
NSERT INTO Batting
FALUES ('HM Amla', '2009-2018', 41, 41, 5,
NSERT INTO Batting
ALUES ('F du Plessis', '2012-2017',
NSERT INTO Batting
ALUES ('DA Miller', '2010-2018',
NSERT INTO Batting
ALUES('GC Smith', '2005-2011', 33, 33, 2,
NSERT INTO Batting
ALUES('Q de Kock','2012-2018', 32, 32, 4,
NSERT INTO Batting
ALUES ('JH Kallis', '2005- 2012',
NSERT INTO Batting
ALUES('JA Morkel',
NSERT INTO Batting
ALUES('F Behardien',
NSERT INTO Batting
77, 71, 22,
1825,
'96*',
37.24,
1468,
124.31, 0,
11,
б,
130,
65);
78, 75, 11,
1672,
'79*',
26.12,
1237,
135.16, 0,
10,
5,
140,
60);
1158,
'97*',
32.16,
883,
131.14, 0,
7,
2,
133,
23);
36, 36, 6,
1129,
119,
37.63,
849,
132.97, 1,
7,
0,
102,
35);
58, 51, 15,
1043,
'101*', 28.97,
745,
140.00, 1,
1,
0,
71,
46);
982,
'89*',
31.67,
770,
127.53, 0,
5,
1,
123,
26);
821,
59,
29.32,
637,
128.88, 0,
2,
3,
97,
24);
25, 23, 4,
666,
73,
35.05,
558,
119.35, 0,
5,
0,
56,
20);
'2005 - 2015',
50, 38, 11,
572,
43,
21.18,
402,
142.28, 0,
0,
1,
29,
39);
'2012-2018',
37, 29, 13,
515,
'64*',
32.18,
402,
128.10, 0,
1,
1,
37,
16);
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
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
- How many patients in the database have been diagnosed with acute bronchitis (ICD9Code = 466) and have a total of 3 or more diagnoses? (diagnosis table; COUNT(*), WHERE, GROUP BY, & HAVING clauses) / Result = 20arrow_forwardWrite and execute a SQL query to list all patient numbers and encounter ids which do not have any data (missing rows) in VisitSummary table. Order the result by patient number, encounter id. Write and execute a SQL query to list all patients whose information about race or gender has changed during their future visits. A future visit is defined as T1.encounter_id < T2.encounter_id. Your output should have patient number, old and new race values, old and new gender values, and the encounter ids. Write and execute a SQL query to list all the patient data for all encounters. Even if patient data is missing in other tables, you still need to list the patient_nbr and encounter_id in your output. Your output should list all the columns (without repeating them) starting with encounter_id, patient_nbr... and it should be ordered by patient_nbr and then encounter_id. Write and execute a SQL query to list all the patient data for all encounters. If the patient data is missing…arrow_forwardUse SQL Developer or Oracle Live SQL to write the appropriate SQL commands as follows: ) PROCEDURE: Use the cust_node table to create a procedure called ‘Get_node_id’ that takes the cust_idas an input parameter to show its corresponding node id to the banker. Then execute this procedure withcust_id=5.arrow_forward
- What use cases are best suited for the CROSS APPLY function in SQL?arrow_forwardSeveral new built-in operations are available in SQL;2008. To simplify, just enumerate the four (4) applications.arrow_forwardHow do you compose a subquery in SQL where you are searching for the most common value in one column for a specific value from another column (i.e. looking for the down where most penalties occur --> down is a column and PlayType is a column with penalties as a value). The columns are in the same table.arrow_forward
- Write an SQL query using the NOT operator that will show all employeeinformation from the Employee table with the exception of EmployeeNumber 7344.arrow_forwardSQL ( ORACLE SQL DEVELOPER ) : THIS IS FIRST , SECOND AND THIRD TABLE for the Task 1 to solve : create table author_45( auth_name char(50), gender char(1), age number(3), nationality char(50), PRIMARY KEY (auth_name) ); create table book_45( isbn number(10), title char(50), ver_no number(2), publisher char(50), PRIMARY KEY (isbn) ); create table writtenBy_45( isbn number(10), auth_name char(50), yr_published number(4), price number(3), no_copy number(4), PRIMARY KEY (isbn,auth_name), foreign key (isbn) references book_45, foreign key (auth_name) references author_45 ); insert into author_45 values ('Benoit','F',42,'French'); insert into author_ 45 values ('Antonini','M',33,'Italian'); insert into author_ 45 values ('McAven','F',31,'Australian'); insert into author_ 45 values ('Williams','M',90,'British'); insert into book_45 values (101,'atabases',1,'Possum'); insert into book_45 values (202,'SQL Primer',2,'Hall'); insert into book_45 values…arrow_forwardHow do I do this question in SQL command? Use the Oracle database employees table and CASE expression to decode the departmentid. Display the department id, last name, salary, and a column called “New Salary” whose value is based on the following conditions: If the department id is 10 then 1.25 * salary If the department id is 90 then 1.5 * salaryIf the department id is 130 then 1.75 * salary Otherwise, display the old salary.arrow_forward
- Write 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_forwardIn what situations might the SOUNDEX function be useful in SQL when querying text data?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
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