Create Tables

Create Tables

Chapter 7

3. / Consider the following tables:
CUSTOMER
CUSTNUMB / CUSTNAME / CUSTADDR / BALANCE / CREDLIM / SLSRNUMB
124 / Adams, Sally / 481 Oak, Lansing, M1 / 418.75 / 500 / 3
256 / Samuels, Ann / 215 Pete, Grant, M1 / 10.75 / 800 / 6
311 / Charles, Don / 48 College, Ira, M1 / 200.10 / 300 / 12
315 / Daniels, Tom / 914 Cherry, Kent, M1 / 320.75 / 300 / 6
405 / Williams, Al / 519 Watson, Grant, M1 / 201.75 / 800 / 12
412 / Adams, Sally / 16 Elm, Lansing, M1 / 908.75 / 1000 / 3
522 / Nelson, Mary / 108 Pine, Ada, M1 / 49.50 / 800 / 12
567 / Baker, Joe / 808 Ridge, Harper, M1 / 201.20 / 300 / 6
587 / Rogerts, Judy / 512 Pine, Ada, M1 / 57.75 / 500 / 6
622 / Martin, Dan / 419 Chip, Grant, M1 / 575.50 / 500 / 3
SLSREP
SLSRNUMB / SLSRNAME / CUSTNUMB
3 / Bill / 124
3 / Bill / 412
3 / Bill / 622
6 / Fred / 256
6 / Fred / 315
6 / Fred / 567
6 / Fred / 587
12 / Tom / 311
12 / Tom / 405
12 / Tom / 522
ORDER
ORDNUMB / ORDDTE / CUSTNUMB
12489 / 90291 / 124
12491 / 90291 / 311
12494 / 90491 / 315
12495 / 90491 / 256
12498 / 90591 / 522
12500 / 90591 / 124
12504 / 90591 / 522
ORDLNE
ORDNUMB / PARTNUMB / NUMBORD / QUOTPRCE
12489 / AX12 / 11 / 14.95
12491 / BT04 / 1 / 402.99
12491 / BZ66 / 1 / 311.95
12494 / CB03 / 4 / 175.00
12495 / CS11 / 2 / 57.95
12498 / AZ52 / 2 / 22.95
12498 / BA74 / 4 / 4.95
12500 / BT04 / 1 / 402.99
12504 / CZ81 / 2 / 108.99
PART
PARTNUMB / PARTDESC / NUMBHAND / ITEMCLSS / WRHSNUMB / UNITPRCE
AX12 / IRON / 104 / HW / 3 / 17.95
AZ52 / SKATES / 20 / SG / 2 / 24.9
BA74 / BASEBALL / 40 / SG / 1 / 4.95
BH22 / TOASTER / 95 / HW / 3 / 34.95
BT04 / STOVE / 11 / AP / 2 / 402.99
BZ66 / WASHER / 52 / AP / 3 / 311.95
CA14 / SKILLET / 2 / HW / 3 / 19.95
CB03 / BIKE / 44 / SG / 1 / 187.50
CX11 / MIXER / 112 / HW / 3 / 57.95
CZ81 / WEIGHTS / 208 / SG / 2 / 108.99
Using these tables, write SQL statements for the following:
(a) For each customer with a $500 credit limit whose balance does not exceed the credit limit, increase the credit limit to $800. / [5]
UPDATECUSTOMER / [1]
SET CREDLIM = 800 / [1]
WHERE CREDLIM = 500
AND BALANCE < CREDLIM / [3]
(b) Find the numbers and names of all sales reps who represent at least one customer with a credit limit of $1000. / [5]
SELECT SLSREP.SLSRNUMB, SLSRNAME
FROM SLSREP, CUSTOMER / [2]
WHERE SLSREP.SLSRNUMB = CUSTOMER.SLSRUNUM / [2]
AND CREDLIM = 1000; / [1]
(c) List, in order, the order total for each order. / [5]
SELECT ORDNUMB, SUM(NUMBORD * QUOTPRCE)
FROM ORDLNE / [3]
GROUP BY ORDNUMB / [1]
ORDER BY ORDNUMB; / [1]
(d) Find the descriptions of all parts included in order 12491. / [5]
SELECT PARTDESC
FROM ORDLNE, PART / [2]
WHERE ORDLNE.PARTNUMB = PART.PARTNUMB / [2]
AND ORDNUMB = 12491; / [1]
2. / Write SQL statements to retrieve data from the following tables:
SUPPLIER
SUPPLIER_NO / SNAME / STATUS / COUNTRY
S1 / JASON / MEDIUM / AUSTRALIA
S2 / TEDDY / LOW / JAPAN
S3 / LESIE / HIGH / CHINA
S4 / BEN / MEDIUM / HONG KONG
S5 / WENDY / HIGH / JAPAN
PART
PART_NO / PNAME / COLOUR / WEIGHT
P1 / DINING TABLE / CHERRY / 15
P2 / COFFEE TABLE / WHITE / 20
P3 / FILE CABINET / BLUE / 12
P4 / TV CABINET / CHERRY / 11
P5 / BOOKCASE / BLUE / 16
P6 / SIDE TABLE / CHERRY / 18
SUPPLIER_PART
SUPPLIER_NO / PART_NO / QUANTITY
S1 / P1 / 0
S1 / P2 / 4
S2 / P3 / 13
S3 / P3 / 4
S3 / P4 / 3
S4 / P3 / 6
S5 / P2 / 7
S5 / P6 / 2
In this question, candidates should not be repeatedly penalised for the same error. For instance, a candidate who consistently omits the final semicolon should be penalised for this error just once.
(a) Create a view named GOOD_SUP from the SUPPLIER table. It should contain all the fields from the original table. Why might such a view be useful? / [4]
(a) /
[3 marks]
Such a view might be useful for maintainability purposes: this view can remain constant, even if extra fields are added to the SUPPLIER table.
[1 mark]
Credit should be given for an alternative credible use for the view.
(b) Due to changes in the manufacturing process, part P3 is to be replaced by a new part (with the same number). The new part will be coloured black and will weigh 2 units more than the original. What SQL command should be used to change the tables? / [4]
(b) /
[4 marks]
(c) The director of marketing is organising a trip to visit some of his suppliers. In order to minimise costs, however, he will only be visiting countries where there is more than one supplier. What SQL query should he use to get all the pairs of supplier names such that the two suppliers are in the same country? / [4]
(c) /
[4 marks]
(d) Find the part numbers for all parts which are supplied by more than one supplier. / [4]
(d) /
[4 marks]
(e) What SQL query will enable the marketing director to find out which suppliers are in the same country as S2? / [4]
(e) /
[4 marks]
(d) / What is the domain? Your answer should include an appropriate example. / [3]
Domain: a pool of values (1 mark), from which one or more attributes draw their actual values (1 mark), e.g., domain of supplier numbers is the set of all possible supplier numbers (1 mark for any suitable example). / [3
The following tables represent data stored by a University department, about students, faculty, classes taught and enrolment for those classes.
(a) / Write an SQL query to obtain a list of course numbers for all those courses which have students enrolled. / [2]
SELECT DISTINCT COURSE#
FROM ENROLMENT;
or
SELECT DISTINCT ENROLMENT.COURSE#
FROM ENROLMENT;
1 mark per line / [2]
(b) / The secretary of the Maths department needs to know the names of all students who are majoring in Maths who have more than 30 credits. Write an SQL query to give her this information. / [3]
SELECT STUNAME
FROM STUDENT
WHERE MAJOR = 'MATH' AND CREDITS > 30;
1 mark per line / [3]
(c) / It has been agreed that Professor Tanaka will teach all of this courses in room B220. Write an SQL statement to change the relevant tables. / [4]
UPDATE CLASS
SET ROOM = 'B220'
WHERE FACID =
(SELECT FACID
FROM FACULTY
WHERE FACNAME = 'Tanaka');
2 marks for the subquery, 2 marks for outer update / [2]
(d) / Write an SQL query to find out the course numbers of all courses which are taught by Byrne of the Maths department. / [5]
SELECT COURSE#
FROM CLASS
WHERE FACID =
(SELECT FACID
FROM FACULTY
WHERE FACNAME = 'Byrne'
AND DEPT = 'Maths'
3 marks for the subquery, 2 marks for outer query
or
SELECT COURSE#
FROM CLASS, FACULTY
WHERE FACNAME ='Byrne'
AND DEPT = 'Maths'
AND CLASS.FACID = FACULTY.FACID;
1 mark per line / [5]
(e) / Find the name and id of the student (or students) who has the largest number of credits. / [2]
SELECT STUNAME STUID
FROM STUDENT
WHERE CREDITS =
(SELECT MAX(CREDITS)
FROM STUDENT);
1 marks for the outer query, 1 marks for subquery / [2]
(f) / If a course has fewer than three students enrolled, it will cancelled. Write an SQL query to find out the course numbers of any courses that might be affected by this. / [2]
SELECT COURSE#
FROM ENROLMENT
GROUP BY COURSE#
HAVING COUNT (*) < 3;
1 marks for the outer query, 1 marks for subquery / [2]
(g) / What do you understand by QBE? Why might QBE be easier to use than SQL / [2]
QBE means 'Query by Example': it is a query language for relational databases (1 mark), where the user fills in a template rather than specifying a structured query explicity (1 mark). / [2]
3. / The following tables show data concerning parts, suppliers and projects.
PART
Pno / Pname / Colour / Weight / City
p1 / nut / red / 12 / London
p2 / bolt / green / 17 / Paris
p3 / screw / blue / 17 / Rome
p4 / screw / red / 14 / London
p5 / cam / blue / 12 / Paris
p6 / cog / red / 19 / London
SUPPLIER
sno / sname / status / city
s1 / Smith / 20 / London
s2 / Jones / 10 / Paris
s3 / Blake / 30 / London
s4 / Clark / 20 / London
s5 / Adams / 30 / Athens
PROJECT
jno / jname / city
j1 / Sorter / Paris
j2 / Display / Rome
j3 / OCR / Athens
j4 / Console / Athens
j5 / RAID / London
j6 / EDS / Oslo
j7 / Tape / London
SP
sno / pno / qty
s1 / p1 / 300
s1 / p2 / 200
s1 / p3 / 400
s1 / p4 / 200
s1 / p5 / 100
s1 / p6 / 100
s2 / p1 / 300
s2 / p2 / 400
s3 / p2 / 200
s4 / p2 / 200
s4 / p4 / 300
s4 / p5 / 400
SPJ
sno / pno / jno / qty
s1 / p1 / j1 / 200
s1 / p1 / j4 / 700
s2 / p3 / j1 / 400
s2 / p3 / j2 / 200
s2 / p3 / j3 / 200
s2 / p3 / j4 / 500
s2 / p3 / j5 / 600
s2 / p3 / j6 / 400
s2 / p3 / j7 / 800
s2 / p5 / j2 / 100
s3 / p3 / j1 / 300
s3 / p4 / j2 / 500
s4 / p6 / j3 / 300
s4 / p6 / j7 / 300
s5 / p5 / j5 / 500
s5 / p5 / j7 / 100
s5 / p6 / j2 / 200
s5 / p1 / j4 / 100
s5 / p4 / j4 / 800
s5 / p6 / j4 / 500
(a) Using the above tables, write SQL statements for the following:
(i) Get full details of all projects in London. / [3]
SELECT *
FROM PROJECT
WHERE city = 'London';
(ii) Find all shipments where the quantity supplied lies in the range 300 to 750 inclusive. For each such shipment, give the supplier number, part number, project number and quantity. / [3]
SELECT *
FROM SPJ
WHERE qty >= 300 and qty <= 750;
or qty BETWEEN 300 and 750
(iii) Get part numbers for all parts supplied by more than one supplier. / [3]
SELECT pno FROM SP
GROUP BY pno
HAVING COUNT(sno)>1;
(iv) Get supplier names for suppliers who supply part p2. / [5]
SELECT sname
FROM SUPPLIER
WHERE sno in
(SELECT sno FROM SP
WHERE pno = 'p2');
(v) Get all part-colour / part-city combinations. / [3]
SELECT
DISTINCT colour, city
FROM PART;
In each part, 1 mark for each line of SQL. Be careful not to penalize more than once for a repeated syntax error (e.g. omission of final ';').
(b) What is the result of the following query? / [3]
SELECT sno, pno FROM SP
WHERE qty >= 300
ORDER BY pno;
sno / pno
s2
s1
s2
s1
s4
s4 / p1
p1
p2
p3
p4
p5
/ [3]
First two rows may be in either order. Lose 1 mark for including any additional column; lose 1 mark for incorrect order. Lose only 1 mark for omission of 1 or 2 rows of the table, provided what remains is correct and in the correct order.
5. / The following tables represent data stored by a University department, about students, faculty, classes taught and enrolment for these classes.
STUDENT
STUID / STUNAME / MAJOR / CREDITS
S1001 / Smith, Tom / History / 90
S1010 / Burns, Edward / Art / 63
S1015 / Jones, Mary / Math / 42
S1002 / Chin, An / Math / 36
S1020 / Rivera, Jane / CSC / 15
S1013 / McCarthy, Owen / Math / 9
FACULTY
FACID / FACNAME / DEPT / RANK
F101 / Adams / Art / Professor
F202 / Smith / History / Associate
F105 / Tanaka / CSC / Instructor
F110 / Byrne / Math / Assistant
F221 / Smith / CSC / Professor
CLASS
COURSE# / FACID / SCHED / ROOM
ART103A / F101 / MWF9 / H221
CSC201A / F105 / TUTHF10 / M110
MTH101B / F110 / MTUTH / H225
HST205A / F202 / MWF11 / H221
MTH103C / F110 / MWF11 / H225
CSC203A / F105 / MTHF1 / M110
ENROLMENT
COURSE# / STUID / GRADE
ART103A / S1001 / A
CSC201A / S1020 / B
CSC201A / S1002 / F
ART103A / S1010
ART103A / S1002 / D
MTH101B / S1020 / A
HST205A / S1001 / C
MTH103C / S1010
MTH103C / S1002 / B
Give SQL statements which will answer the following queries using the tables above:
(a) Get the names and Ids of all faculty members, arranged in alphabetical order by name. / [2]
Select Facname, FacId
>From Faculty
Order by Facname;
[2 marks]
(b) Find the Ids and names of all students taking ART103A. / [4]
Select Enrolment.stuid, Stuname
>From Student, Enrolment
where Course# = 'ART103A'
AND Enrolment.Stuid = Student.Stuid;
[4 marks]
(c) Find the total number of students of all MTH103C. / [4]
Select Count(Distinct Stuid)
>From Enrolment
Where Coruse# = 'MTH103C';
[4 marks]
(d) Get full details of all MTH courses. / [4]
Select *
>From Class
Where Course# Like 'MTH%';
[4 marks]
(e) Change the major of S1013 from Math to Null. / [3]
Update Student
Set Major = Null
Where Stuid = 'S1013';
[3 marks]
(f) Add a new record to the faculty table, with Id of F330, name of Jones, department of CSC, and rank of Instructor. / [3]
Insert
Into Faculty (Facid, Facname, Dept, Rank)
Values ('F330', 'Jones', 'CSC', 'Instructor');
[3 marks]
Consider the following database.



Write SQL queries to accomplish the following tasks:
(a) / Display a list of the names of all students in alphabetical order.
SELECT student_name FROM student (1 mark)
ORDER BY student_name (1 mark) / [2]
(b) / Display a list of names of all subjects offered by the Computer Science department.
SELECT subject_name FROM subject (1 mark)
WHERE department =‘Computer Science ’(1 mark) / [2]
(c) / Calculate the number of students.
SELECT count(*) FROM student (1 mark) / [1]
(d) / Calculate the average mark so far.
SELECT AVG(mark) FROM enrolment / [1]
(e) / Find the subject ids of subjects that a pass rate of 100%. (Assume that the pass mark is 50).
SELECT subject_id FROM enrolment (1 mark)
GROUP BY subject_id, student_id (1 mark)
HAVING MIN(mark) >= 50 (1 mark) / [3]
(f) / Find the subject ids of subjects for which the enrolment is less than that for CS1200.
SELECT subject_id FROM enrolment e1 (1 mark)
GROUP BY subject_id (1 mark)
HAVING COUNT(*) <
(SELECT COUNT(*) FROM enrolment e2
WHERE e2.subject_id =‘CS1200 ’) (1 mark) / [3]
(g) / Find subject ids of subjects that have no students enrolled.
SELECT subject_id FROM subject (1 mark)
WHERE NOT EXISTS (1 mark)
(SELECT *
FROM enrolment
WHERE subject.subject_id = enrolment.subject _id)
(1 mark) / [3]
(f) / List four operations that can be executed by the Data Definition Language (DDL) component of the Structured Query Language (SQL). / [4]
Possible Data Definition Language (DDL) operations are:
  • Create Tables
  • Modify Tables (ALTER)
  • Create Indexes
  • Drop Tables
  • Drop Indexes
  • Authorization (GRANT)
  • Reauthorization (REVOKE)

Consider the following four tables.
Customer
CUSTNUM / CUSTNAME / CUSTADDR / BALANCE / CREDLIM / REPNUMB
124 / Adams, Sally / 481 Oak, Lansing / 418.75 / 500 / 3
256 / Samuels, Ann / 215 Pete, Grant / 10.75 / 800 / 6
311 / Charles, Don / 48 College, Ira / 200.10 / 300 / 12
315 / Daniels, Tom / 914 Cherry, Kent / 320.75 / 300 / 6
405 / Wiliams, Al / 519 Watson, Grant / 201.75 / 800 / 12
412 / Adams, Sally / 16 Elm, Lansing / 908.75 / 1000 / 3
522 / Nelson, Mary / 108 Pine, Ada / 49.50 / 800 / 12
567 / Baker, Joe / 808 Ridge, Harper / 201.20 / 300 / 6
587 / Rogerts, Judy / 512 Pine, Ada / 57.75 / 500 / 6
622 / Martin, Dan / 419 Chip, Grant / 575.50 / 500 / 3
Order
ORDNUMB / ORDDTE / CUSTNUMB
12489 / 90291 / 124
12491 / 90291 / 311
12494 / 90491 / 315
12495 / 90491 / 256
12498 / 90591 / 522
12500 / 90591 / 124
12504 / 90591 / 522
ORDLNE
ORDNUMB / PARTNUMB / NUMBORD / QUOTPRCE
12489 / AX12 / 11 / 14.95
12491 / BT04 / 1 / 402.99
12491 / BZ66 / 1 / 311.95
12494 / CB03 / 4 / 175.00
12495 / CS11 / 2 / 57.95
12498 / AZ52 / 2 / 22.95
12498 / BA74 / 4 / 4.95
12500 / BT04 / 1 / 502.99
12504 / CZ81 / 2 / 108.99
PART
PARTNUMB / PARTDESC / UNONHAND / ITEMCLSS / WRHSNUMB / UNITPRCE
AX12 / IRON / 104 / HW / 3 / 17.95
AZ52 / SKATES / 20 / SG / 2 / 24.9
BA74 / BASEBALL / 40 / SG / 1 / 4.95
BH22 / TOASTER / 95 / HW / 3 / 34.95
BT04 / STOVE / 11 / AP / 2 / 402.99
BZ66 / WASHER / 52 / AP / 3 / 311.95
CA14 / SKILLET / 2 / HW / 3 / 19.95
CB03 / BIKE / 44 / SG / 1 / 187.50
CX11 / MIXER / 112 / HW / 3 / 57.95
CZ81 / WEIGHTS / 208 / SG / 2 / 108.99
Based on the tables above, write SQL statements to carry out the following tasks:
(a) / Create the PART table, using the following table definition.
PART
Column / Type / Length / Decimal Places
PARTNUMB / Char / 4
PARTDESC / Char / 10
UNONHAND / Numeric / 3 / 0
ITEMCLSS / Char / 2
WRHSNUMB / Numeric / 1 / 0
UNITPRCE / Numeric / 7 / 2
/ [4]
CREATE TABLE PART
(PARTNUMB CHAR(4), PARTDESC CHAR(10),
UNONHAND NUMERIC(3,0), ITEMCLSS CHAR(2),
WRHSNUMB NUMERIC(1,0) UNITPRCE NUMERIC(7,2));
(b) / From the CUSTOMER table, which records can be removed without introducing any anomaly? Justify your answer. Give an SQL statement to remove one of these records. / [3]
Customers with CUSTNUMB 405,412,567,587 and 622
NB: all 5 records needed!
This is possible as these customers do not have any order transactions. Removal of these records will therefore not cause any anomaly.
DELETE FROM CUSTOMER
WHERE CUSTNUMB = 405;
(c) / Find the number, name, current balance and sales rep number of those customers whose balance is greater than the balance of every customer of sales rep 12. / [6]
SELECT CUSTNUM, CUSTNAME, BALANCE, REPNUMB
FROM CUSTOMER
WHERE BALANCE > ALL (SELECT BALANCE
FROM CUSTOMER WHERE REPNUMB = 12);
(d) / Create a view LISTING with headers ORDER_NUMBER and ORDER_TOTAL to list the order total for those orders whose total is more than $200. / [5]
CREATE VIEW LISTING (ORDER_NUMBER, ORDER_TOTAL)
AS
SELECT ORDNUMB, SUM(NUMBORD * QUOTPRCE)
FROM ORDLNE
GROUP BY ORDNUMB
HAVING SUM(NUMBORD * QUOTPRCE) > 200;
(e) / Give two advantages of using the VIEW command. / [2]
Use of a view can allow someone access to just the rows and columns that they need from the database tables, particularly restricting them from irrelevant and sensitive information.
By assigning a view name, users can retrieve the required information just by selecting the already created view by its assigned name, without having to explicitly query the database using the data manipulation language component of SQL.
(g) / What is Structured Query Language? / [2]
Structured Query language is a query language for relational database whereby user specify the structured query explicitly.
(a) / What is meant by a referential integrity constraint? / [2]
Referential Integrity Constraint states that object of a data structure is related with other object of another data structure.
(b) / (i) With respect to referential integrity rules, what is an insertion rule? Give an example to illustrate your answer. / [4]
Insertion rule
a row should not be inserted into referencing table unless there already exist a matching entity in referenced table.
example :-
PART
PNO / PDESCRIPTION / SNO
1 / RED BALL / 40
2 / BLUE BALL / 50
SUPPLIER
SNO / SNAME
40 / BINGO
45 / LINGO
With reference to the tables, 2 BLUEBALL 50 should not be inserted into PART table (referencing table) due to SNO =50 does not exist in SUPPLIER table (referenced table).
(ii) What is deletion rule? Describe, using examples as necessary, three types of deletion rules. / [8]
Deletion rule
a row should not be deleted from the referenced table, if there exists a matching row in referencing table.
three types of deletion rule are : - restrict, cascade, and nullify.
Example : -
PART
PNO / PDESCRIPTION / SNO
1 / RED NUT / 40
2 / BLUE NUT / 40
3 / GREEN NUT / 45
SUPPLIERS
SNO / SNAME
40 / BINGO
45 / LINGO
55 / KING
Restrict
By using the example shown, supplier with number 40 and 45 cannot be deleted, but supplier with number 55 can be deleted from SUPPLIER table.
Cascade
If supplier number 45 have to be deleted from SUPPLIER table, PART information that referenced supplier number 45 will be deleted too.
Nullify
If supplier number is 40 is deleted from SUPPLIER table, PART's SNO 40 referenced by Part number 1 and 2 will be NULL.
(a) / Define each of the following terms, with reference to SQL and the relational database model:
(i) view / [2]
It's a mask upon the tables. Due this method the programmers then restrict the data from users to display that. It's always creates on the base table.
(ii) equi-join / [2]
This joining method is that the tables are joined on columns which should have the same data type and data width.
(iii) natural join / [2]
It's similar as equi-join. The only difference is that it has one extra duplicate column in the result table.
(iv) subquery / [2]
The subquery is an SQL statement also known as nested query. The data retrieved from the sub query is known as nested statement.
(v) intersect / [2]
Of in the DML, its purpose is to join the two tables on the basis of their data types. Its a join statement.
(b) / Describe briefly the two components of Structured Query Language (SQL), giving an example command for each component and explaining what it does. / [10]
The two components of SQL are
(i) Data Manipulation Language
(ii) Data Definition Language
(i) Data Manipulation Language
It's used to create entries in the catalog or repository or data dictionary. Its purpose is to:-
- create table
- modify table (alter)
- create index
- drop index
- create view
-drop view
The command in DML is
SQL > Create INDEX ON SUPPLIER (S_No);
By this command I am going to create an index on the base table or target (supplier). By this command the RDBMS then provides the Random or sequential access on the base table.
(ii) Data Definition Language
The DDL is divided into two parts i.e retrieving and updating of data. On this will have eight join statements. By this facility, the user can
- retrieve the data
- join the data
- updating the data
-deleting the data
-functional criteria
The command for this is
SQL > SELECT * FROM EMP -> target table
where EMP_NO = 1234;
The * mean all the data from target table (EMP) on the selection criteria. This command selects only a particular type of an employee on selection criteria. The WHERE is an selection criteria.

(g) Consider the following table:

Give SQL statements to retrieve the following information from the above
table:
(i) Spencer's details
(ii) The number of students
(iii) The number of students with an average of greater than 50
(iv) The details of students with an average of greater than 50 [8]
i) Select * from Students [1]
Where Name = 'Spencer' [1]
ii) Select Count (*) [1]
From Students [1]
iii) Select Count (Distinct Student number) from Students [1]
Where Average>50 [1]
iv) Select * from Students [1]
Where Average>50 [1]

Top View