6. Basic SQL – 4marks

Question 1

The schema for SQL questions in 2018 Sem 1 exam will be:

Answer 1

DEVICE [dno, dtype, price]
PROVIDER [pno, pname, web]
SERVICE [dno, pno, servicedate]

SERVICE.dno references DEVICE.dno
SERVICE.pno references PROVIDER.pno

Question 2

Data in a relational database can be manipulated in the following ways:

Answer 2

• INSERT: New tuples may be inserted
• DELETE: Existing tuples may be deleted
• UPDATE: Values of attributes in existing tuples may be changed
• RETRIEVE: Attributes of specific tuples, entire tuples, or even entire relations may be retrieved

Relational Query Languages should provide all of the above

Question 3

Relational Queries are formulated in Relational Query Languages. Three types are:

Answer 3

Relational Algebra (RA) 
• Formal query language for a relational database 

Structured Query Language (SQL)
• Implementation of RA
• Comprehensive, commercial query language with widely accepted international standard

Query by Example (QBE)
• Commercial, graphical query language with minimum syntax

Question 4

SQL is designed for:

Answer 4

• data definition,
• data manipulation, and
• data control

Question 5

SQL is what type of language?

Answer 5

Declarative

Question 6

How does a declarative language work?

Answer 6

Users specify what the result of the query should be, DBMS decides operations and order of execution

Question 7

Relational algebra is a procedural language. How does it work?

Answer 7

Algebraic expressions specify an order of operations (ie. how the query will be processed)

Question 8

SQL commands can be divided into four subgroups, DDL, DML, DCL, and TCL. Describe the role of the first three:

Answer 8

Acronym to remember the three groups: DMC
• Data Definition Language (DDL): Provides commands to create and modify database structure and constraints.
• Data Manipulation Language (DML): Provides commands to insert, delete, update and retrieve
• Data Control Language (DCL): Provides commands to control the access of data