Structured query langauge (SQL)

Question 1

What is SQL?

Answer 1

‘Structured Query Language’, that can do data definition, management, access control, and queries as well as control flow.

Question 2

Is SQL declarative or procedural?

Answer 2

It is declarative even though it also includes some procedural elements.

Question 3

How many values does BOOLEAN have

Answer 3

3:

TRUE, FALSE, UNKOWN (accounts for null)

Question 4

How can you check if a value is null in SQL?

Answer 4

IS NULL and IS NOT NULL

Question 5

How do you check the length of a char in a column?

Answer 5

SELECT CHAR_LENGTH(“Philiong”) AS LengthOfString;

Question 6

which methods can you use when dealing with dates?

Answer 6

CURRENT_TIME, CURRENT_DATE, CURRENT_TIMESTAMP

Question 7

How do you specify that an attribute may not be null?

Answer 7

add the: NOT NULL constraint

Question 8

How do you define a default value to an attribute?

Answer 8

when you define an attribute add the clause DEFAULT to the attribute definition.

Question 9

What value will an attribute be given if no value or default is provided?

Answer 9

NULL

Question 10

How can you make sure that an added value is between a certain range?
(When declaring an attribute for a table with a statement)

Answer 10

add the CHECK keyword to a definition like this : age INT NOT NULL CHECK (age > 0 AND age < 121)

Question 11

What is the default action that SQL uses for referential integrity?

Answer 11

NO ACTION

Question 12

What does the NO ACTION referential integrity do

Answer 12

It rejects the operation if it will cause a violation

Question 13

How can you get around the NO ACTION rejection?

Answer 13

You can attach a triggered action on any foreign key constraint like SET NULL, CASCADE and SET DEFAULT that can trigger on the ON DELETE or ON UPDATE

Question 14

What is an ASSERTION?

Answer 14

It is a check that can involve multiple tables.

e.g:
CREATE ASSERTION MaxNumberOfEmployee CHECK (NOT EXISTS (SELECT Eid
FROM Employee
GROUP BY Eid
HAVING COUNT(*) > 20))

Question 15

What does the schema do in a database system?

Answer 15

It defines tables, relationships and the fields of the tables.

Question 16

What is the syntax for creating a database?

Answer 16

CREATE DATABASE IF NOT EXISTS database_name

Question 17

What is the syntax for creating tables?

Answer 17

CREATE TABLE Employee

Question 18

What is the syntax for deleting tables?

Answer 18

DROP TABLE IF EXISTS Employee

Question 19

What is the syntax for changing tables?

Answer 19

ALTER TABLE Employee

ADD last_name VarChar(20 NOT NULL)

Question 20

What are the commands for

1. retrieving data
2. inserting data
3. updating data
4. deleting data

Answer 20

1. select
2. insert
3. update
4. delete

Question 21

What is the syntax for retrieving data?

Answer 21

SELECT (DISTINCT | ALL ) ( * | Attribute) (AS newName)
FROM tableName
WHERE condition

Question 22

How can you make aliases in SQL?

Answer 22

SELECT Name AS FirstName

FROM Employee e

Question 23

How can you insert?

Answer 23

INSERT is used to add a single row to a table

INSERT INTO EMPLOYEE (Fname, Ssn) VALUES (‘Andrea’, ‘1234’);

Question 24

How can you delete?

Answer 24

DELETE FROM table_name WHERE

only DELETE FROM table_name all records will be deleted

Question 25

How can you update?

Answer 25

UPDATE Employee SET Salary = Salary * 1.1 WHERE Dno = 5;