SQL Database

Question 1

What is Database ?

Answer 1

Organized collection of data
Storage space for content/information (data)

Advantages:
• Control of data accuracy
• Data consistency
• Data manipulation (sort, retrieve, update…)
• Data Security

Disadvantages:
• Complexity, size, cost, maintenance

Question 2

What is a Relational database? Advantages?

Answer 2

Relational databases use TABLES to store information.
It allows you to easily find specific information, sort based on any field, and generate reports that contain only certain fields from each record.

Question 3

What is Schema?

Answer 3

Logical container for database objects (tables, views, triggers) that user creates

Question 4

What is a Table? Can we have an empty Table?

Answer 4

Table is set of data elements (values) that is organized using columns (fields) and rows (records)
We can have an empty table

Question 5

What is Field? What ‘part’ of the table?

Answer 5

Field is a database storage simplest unit (table column)

Question 6

What is Record? What ‘part’ of the table?

Answer 6

Record is a row and it represents a single structured data set in table

Question 7

What is Query?

Answer 7

Query is your request to the database to retrieve information

Question 8

What is Report? How much information can you get in one report?

Answer 8

Report is the returned information to the specified query

Reports can be tailored to the needs of the data-user, making the information they extract much more useful

Question 9

What is DBMS? Name a few popular databases

Answer 9

Database Management System is a software that controls the organization, storage, retrieval, security and integrity of data in dB

Popular Databases: ORACLE, Sybase, MySQL , DB2.

Question 10

What is a PRIMARY KEY (PK) / FOREIGN KEY (FK)?

Answer 10

PRIMARY KEY (PK) is a unique identifier of every record in a table;

FOREIGN KEY (FK) is a column (or combination of columns) that is used to establish a relationship between the tables;
Foreign key is usually not unique (one-to-many relation) and shall always point to a primary key

Question 11

What is a database NORMALIZATION?

Answer 11

NORMALIZATION is the dB process of dividing large tables into smaller tables and defining relationships between them;

Question 12

What is SQL stands for? What does it do?

Answer 12

SQL stands for Structured Query Language. It is a database computer language, designed to retrieve and manage data, create and modify dB schema

Question 13

Name a few Schema commands; Describe

Answer 13

CREATE - creates a table (index, view) in a database.
CREATE TABLE table1 (ID int, Last_Name varchar(255))

ALTER - used to add, delete, or modify columns in an existing table.
ALTER TABLE table1 ADD column1 varchar(255)
ALTER TABLE table1 DROP COLUMN column1
ALTER TABLE table1 MODIFY column1 varchar (255)

DROP - deletes a table (index, view).
DROP TABLE table1

TRUNCATE - deletes data in the table, but not table itself
TRUNCATE TABLE table1

COMMENT

RENAME

Question 14

How to add a record to the table?

Answer 14

INSERT INTO table1 VALUES

Question 15

How to change one field in the table?

Answer 15

ALTER TABLE table1 (MODIFY or RENAME COLUMN)
UPDATE table1 SET value1 WHERE field1=‘value2’

Question 16

What is the difference between DROP, DELETE and TRUNCATE?

Answer 16

TRUNCATE removes ALL records from the table, same as DELETE FROM without WHERE clause
DROP deletes whole table

Question 17

What is Constraint ? Give couple constraints examples?

Answer 17

Constraint is used to define data integrity, restrict the values in a database
NOT NULL, PK, FK

Question 18

How to read data from dB?

Answer 18

SELECT * FROM table1

Question 19

What are the minimum requirements for the SELECT statement?

Answer 19

Two: SELECT, FROM

Question 20

Is WHERE required in the SELECT statement? Why?

Answer 20

Only SELECT and FROM clause are required. WHERE clause is used to specify the certain condition, not select the whole table

Question 21

How can I retrieve information for the whole table?

Answer 21

SELECT * FROM table1

Question 22

How would you read all first names from CUSTOMER table?

Answer 22

SELECT first_name FROM customer

Question 23

Is it possible to have duplicate first names in CUSTOMER table? How would you read only unique names?

Answer 23

SELECT DISTINCT first_name FROM customer

Question 24

How would you retrieve all employees 55+ years old, living in cities, starting with ‘San’, showing the oldest employee first?

Answer 24

SELECT * FROM employees
WHERE age>55 AND city LIKE ‘San%’
ORDER BY age DISC

Question 25

How would you read all employees who was born in Sep, Oct, and Nov?

Answer 25

SELECT * FROM employees WHERE to_char (birth_date, ‘mon’) IN (‘sep’, ‘oct’, ‘nov’)

Question 26

How would you choose all employees who do NOT have the address, stored in the system?

Answer 26

SELECT * FROM employees WHERE address IS NULL

Question 27

GROUP BY, HAVING, ORDER BY are required. True or False? Why?

Answer 27

They are optional, but must be in specific order

Question 28

How can i group result by specified criteria

Answer 28

GROUP BY used with aggregate functions COUNT(), MAX(), MIN(), SUM(), AVG()

Question 29

When are we using HAVING option?

Answer 29

After GROUP BY, we cant use WHERE, we use HAVING

Question 30

What is an aggregate function? Examples?

Answer 30

It return a single value, calculated from values in a column
COUNT()
MAX()
MIN()
SUM()
AVG()

Question 31

How can I count all records within a table?

Answer 31

SELECT COUNT(*) FROM table1

Question 32

How can I count all the records within the department?

Answer 32

SELECT COUNT(*) FROM table1 WHERE department_id=80

Question 33

How can I find the average salary?

Answer 33

SELECT AVG(salary) FROM table1