VA Session 3 & 4

Question 1

SQL

Answer 1

• Structured Query Language
• standard relational database language (can be used with majority of relational DBMS software tools)

Question 2

SQL Use

Answer 2

• Create databases
• Add, modify & delete a) database structures & b) records in database
• Query databases (data retrieval)

Question 3

Case sensitivity

Answer 3

• No: keywords, table & column names – SELECT = select = SeLeCt; but commands usually written with capital letters, table etc. names small
• yes: Values of columns with textual types

Question 4

SQL Commands Categories

Answer 4

1. Data Definition Language (DDL)
   create & modify structure of database e.g. CREATE, ALTER, DROP
2. Data Manipulation Language (DML)
   Insert, modify, delete & retrieve data e.g. INSERT TO, UPDATE, DELETE, SELECT
3. Data Control Language (DCL)
4. Transactional Control Language (TCL)

Question 5

SQL data types

Answer 5

• Each column of each SQL created relation has a specific data type

Question 6

CHAR(n)

Answer 6

fixed length n-character string

Question 7

VARCHAR(n)

Answer 7

variable length character string with max. size of n characters

Question 8

NUMERIC(x,y)

Answer 8

number with x digitis, y of which are after decimal point

Question 9

Subqueries

Answer 9

• queries within a larger SQL query
• Inner queries used to return information that will be use by outer query
• Enclosed by ()
• usually added within WHERE clause of outer query
• e.g. SELECT … FROM … WHERE … IN (SELECT … FROM …. WHERE…)

Question 10

Joining data from several datas - ways

Answer 10

• 1) WHERE clause
• 2) JOIN clause (sometimes preferred due to readability)
• 3) operators as UNION or INTERSECTION

Question 11

Alias

Answer 11

• Alternative name usable in a query instead of full name
• Pro: shorter & if 2 tables have same name
• e.g. SELECT p.productid, v.vendorname FROM product p, vendor v ;

Question 12

Renaming

Answer 12

• renaming columns that appear in result
• e.g. p.productid pid, v.vendorname vname FROM product p, vendor v ;

Question 13

Views

Answer 13

• Mechanism in SQL to save structure of a query in RDBMS
• virtue table <-> not actual table: if view invoked, executes a query & retrieves data from table
• virtual table whose contents are defined by a query
• used to focus, simplify, and customize the perception each user has of the database
• security mechanisms by letting users access data through the view, without granting the users permissions to directly access the underlying base tables of the view
• Like table: view consists of a set of named columns and rows of data that come from tables referenced in the query defining the view and are produced when view referenced
• acts as a filter on the underlying tables referenced in the view

Question 14

Set operators

Answer 14

• combine results of >= 2 SELECT statements (, querying relations, views or other SELECT queries) that are union compatible (= contain same number of columns & data types of columns in sets match)
• Union, intersection & difference

Question 15

Absolute value of x

Answer 15

ABS(x)

Question 16

Length of string s

Answer 16

LENGTH(s)
e.g. Return products with name that has 5 characters:
SELECT *
FROM Products
WHERE LENGTH(name) = 5;

Question 17

Converts string s to lowercase & uppercase

Answer 17

LOWER(s)
UPPER(s)

Question 18

Larger & smaller value of x and y

Answer 18

MAX(x,y)
MIN(x,y)

Question 19

Produces a random number

Answer 19

RANDOM()

Question 20

Number x rounded to d decimal places

Answer 20

ROUND(x,d)

Question 21

Calculating a cumulative

Answer 21

SUM

Question 22

Null returned

Answer 22

there is no value returned with the query (!= 0 -> if at any point of calculation / comparison NULL, the result will be NULL)