Database/SQL

Question 1

A _________ is a collection of information that is organized so that it can be easily accessed, managed and updated.

Answer 1

database

Question 2

A _________ database is a type of database. It uses a structure that allows us to identify and access data in relation to another piece of data in the database.

Answer 2

relational

Question 3

Often, data in a ________ database is organized into tables.

Answer 3

relational

Question 4

Tables can have hundreds, thousands, sometimes even millions of rows of data. These rows are often called ______.

Answer 4

records

Question 5

_______ are labeled with a descriptive name (say, age for example) and have a specific _____ _____.

Answer 5

Columns are labeled with a descriptive name (say, age for example) and have a specific data type.

Question 6

A ______ _______ ______ ______ (_____) is a program that allows you to create, update, and administer a relational database

Answer 6

relational database management system (RDBMS)

Question 7

What does SQL stand for?

Answer 7

Structured Query Language

Question 8

___ (________ _____ ________) is a programming language used to communicate with data stored in a relational database management system

Answer 8

SQL (Structured Query Language)

Question 9

SQLite is a relational database management system

True or False

Answer 9

True

Question 10

_____ contains a minimal set of SQL commands (which are the same across all RDBMSs). Other RDBMSs may use other variants.

Answer 10

SQLight

Question 11

Why do we need SQL?

1.

2.

3.

4.

5.

6.

7.

Answer 11

• Allows users to access data in the relational database management systems.
• Allows users to describe the data.
• Allows users to define the data in a database and manipulate that data.
• Allows to embed within other languages using SQL modules, libraries & pre-compilers.
• Allows users to create and drop databases and tables.
• Allows users to create view, stored procedure, functions in a database.
• Allows users to set permissions on tables, procedures and views.

Question 12

Describe a simple SQL Architecture

Answer 12

Question 13

A _____ ___ is a field in a table which uniquely identifies each row/record in a database table.

Answer 13

primary key

Question 14

Primary keys must contain ______ ______.

Answer 14

Unique Values

Question 15

A primary key column can have NULL values.

True or False

Answer 15

False

A primary key cannot have NULL values

Question 16

A table can have only one primary key

True or False

Answer 16

True

Question 17

A primary key can only consist of a single field.

True or False

Answer 17

False

A primary key may consist of a single or multiple keys.

Question 18

When multiple fields are used as a primary key, they are called a ________ ___.

Answer 18

composite key

Question 19

If a table has a primary key defined on any field(s), then you can have two records having the same value of that field(s).

True or False

Answer 19

False

If a table has a primary key defined on any field(s), then you cannot have two records having the same value of that field(s).

Question 20

Generally, the primary key is created while creating the database and the table.

True or False

Answer 20

True

Question 21

The primary key can’t be created after the creation of the table as shown below.

True or False

Answer 21

False

The primary key can also be created after the creation of the table as shown below.

Question 22

Set a primary key on this column ID

CREATE TABLE CUSTOMERS(

ID INT NOT NULL, // To set a primary key on this column ID

NAME VARCHAR (20) NOT NULL,

AGE INT NOT NULL,

ADDRESS CHAR (25) ,

SALARY DECIMAL (18, 2),

PRIMARY KEY (ID)

);

Answer 22

ALTER TABLE CUSTOMER ADD PRIMARY KEY (ID);

Question 23

A _____ ___ is a key used to link two tables together.

Answer 23

foreign key

Question 24

A foriegn key is sometimes called a _______ ___

Answer 24

referencing key

Question 25

A Foreign Key is a column or a combination of columns whose values match a ______ \_\_\_\_ in a different table.

Answer 25

**Primary Key**

Question 26

The relationship between 2 tables matches the ______ \_\_\_ in one of the tables with a ______ \_\_\_ in the second table.

Answer 26

The relationship between 2 tables matches the **Primary Key** in one of the tables with a **Foreign Key** in the second table.

Question 27

Consider the following two tables, "Customers" and "Orders". Once the tables have been created, what syntax could create the foreign key? ## Footnote CREATE TABLE CUSTOMERS( ID INT NOT NULL, NAME VARCHAR (20) NOT NULL, AGE INT NOT NULL, ADDRESS CHAR (25) , SALARY DECIMAL (18, 2), PRIMARY KEY (ID) ); CREATE TABLE ORDERS ( ID. INT NOT NULL, DATE DATETIME, CUSTOMER\_ID INT references CUSTOMERS(ID), AMOUNT double, PRIMARY KEY. (ID) );

Answer 27

ALTER TABLE ORDERS ADD FOREIGN KEY (Customer\_ID) REFERENCES CUSTOMERS (ID);

Question 28

What does DDL mean?

Answer 28

Data Definition Language

Question 29

What are the 3 main commands of DDL?

Answer 29

**CREATE**, **ALTER**, **DROP** ## Footnote **CREATE** - is used to create the database or its objects (like table, index, function, views, store procedure and triggers). **ALTER** - is used to alter the structure of the database. **DROP** - used to delete objects from the database TRUNCATE - is used to remove all records from a table, including all spaces allocated for the records are removed. COMMENT –is used to add comments to the data dictionary. RENAME –is used to rename an object existing in the database.

Question 30

What does DML Mean

Answer 30

**Data Manipulation Language​** SELECT - is used to retrieve data from the a database. INSERT - is used to insert data into a table. UPDATE - is used to update existing data within a table. DELETE - is used to delete records from a database table.

Question 31

What are the commands of DQL

Answer 31

Select - Insert - Update - Delete SELECT - is used to retrieve data from the a database. INSERT - is used to insert data into a table. UPDATE - is used to update existing data within a table. DELETE - is used to delete records from a database table.

Question 32

What does DCL mean?

Answer 32

**Data Control Language**

Question 33

What are the commands of **DCL**?

Answer 33

**GRANT** - **REVOKE** **GRANT** - gives user’s access privileges to database. **REVOKE** - withdraw user’s access privileges given by using the GRANT command.

Question 34

What does TCL mean?

Answer 34

**Transaction Control Language**

Question 35

What are the 3 TCL main statements/commands?

Answer 35

**SAVEPOINT**, **ROLLBACK**, **COMMIT** **COMMIT** – commits a Transaction. **ROLLBACK** – rollbacks a transaction in case of any error occurs. **SAVEPOINT** –sets a savepoint within a transaction.

Question 36

## Footnote The SQL ______ statement is used to fetch the data from a database table which returns this data in the form of a result table. These result tables are called \_\_\_\_\_\_\_\_\_\_.

Answer 36

## Footnote The SQL **SELECT** statement is used to fetch the data from a database table which returns this data in the form of a result table. These result tables are called **result-sets**. ex. SELECT column1, column2, columnN FROM table\_name;

Question 37

What syntax would you use to fetch all the fields available in the field?

Answer 37

SELECT \* FROM table\_name;

Question 38

Consider the following "Customers" table. Select only the **ID**, **NAME** and **SALARY** columns. ## Footnote +----+----------+-----+-----------+----------+ | ID | NAME | AGE | ADDRESS | SALARY | +----+----------+-----+-----------+----------+ | 1 | Ramesh | 32 | Ahmedabad | 2000.00 | | 2 | Khilan | 25 | Delhi | 1500.00 | | 3 | kaushik | 23 | Kota | 2000.00 | | 4 | Chaitali | 25 | Mumbai | 6500.00 | | 5 | Hardik | 27 | Bhopal | 8500.00 | | 6 | Komal | 22 | MP | 4500.00 | | 7 | Muffy | 24 | Indore | 10000.00 | +----+----------+-----+-----------+----------+

Answer 38

SELECT ID, NAME, SALARY FROM CUSTOMERS;

Question 39

The SQL **\_\_\_\_\_\_** clause is used to specify a condition while fetching the data from a single table or by joining with multiple tables. If the given condition is satisfied, then only it returns a specific value from the table.

Answer 39

**WHERE**

Question 40

You should use the **\_\_\_\_\_** clause to filter the records and fetching only the necessary records.

Answer 40

**WHERE**

Question 41

The WHERE clause is only used in the SELECT statement. **True** or **False**

Answer 41

**FALSE** The **WHERE** clause can be used in the **SELECT,** **UPDATE &** **DELETE** statement.

Question 42

Using the "Customers" table retrieve all the names of customers who's age is greater than 20. +----+----------+-----+-----------+----------+ | ID | NAME | AGE | ADDRESS | SALARY | +----+----------+-----+-----------+----------+ | 1 | Ramesh | 32 | Ahmedabad | 2000.00 | | 2 | Khilan | 25 | Delhi | 1500.00 | | 3 | kaushik | 23 | Kota | 2000.00 | | 4 | Chaitali | 25 | Mumbai | 6500.00 | | 5 | Hardik | 27 | Bhopal | 8500.00 | | 6 | Komal | 22 | MP | 4500.00 | | 7 | Muffy | 24 | Indore | 10000.00 | +----+----------+-----+-----------+----------+

Answer 42

**SELECT CUSTOMERS.AGE from CUSTOMERS where AGE \> 20;** NOTE:Can use DB name as prefix before column name.

Question 43

The SQL ___________ Statement is used to add new rows of data to a table in the database.

Answer 43

**INSERT INTO**

Question 44

Use an INSERT INTO statement to add data into each column of the following table. +----+----------+-----+-----------+----------+ | ID | NAME | AGE | ADDRESS | SALARY | +----+----------+-----+-----------+----------+ | 1 | Ramesh | 32 | Ahmedabad | 2000.00 | | 2 | Khilan | 25 | Delhi | 1500.00 | | 3 | kaushik | 23 | Kota | 2000.00 | | 4 | Chaitali | 25 | Mumbai | 6500.00 | | 5 | Hardik | 27 | Bhopal | 8500.00 | | 6 | Komal | 22 | MP | 4500.00 | | 7 | Muffy | 24 | Indore | 10000.00 | +----+----------+-----+-----------+----------+

Answer 44

**INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)** **VALUES (1, 'Ramesh', 32, 'Ahmedabad', 2000.00 );**

Question 45

The SQL ____ clause is used to combine records from two or more tables in a database. A ____ is a means for combining fields from two tables by using values common to each.

Answer 45

The SQL **Joins** clause is used to combine records from two or more tables in a database. A **JOIN** is a means for combining fields from two tables by using values common to each.

Question 46

Perform innner join these two tables - Join the **AMOUNT** column from **ORDERS** table to the to the **ID**, **NAME** & **AGE** of the **CUSTOMERS** table by the **CUSTOMER\_ID** and **ID** in each table **Customers Table** +----+----------+-----+-----------+----------+ | ID | NAME | AGE | ADDRESS | SALARY | +----+----------+-----+-----------+----------+ | 1 | Ramesh | 32 | Ahmedabad | 2000.00 | | 2 | Khilan | 25 | Delhi | 1500.00 | | 3 | kaushik | 23 | Kota | 2000.00 | | 4 | Chaitali | 25 | Mumbai | 6500.00 | | 5 | Hardik | 27 | Bhopal | 8500.00 | | 6 | Komal | 22 | MP | 4500.00 | | 7 | Muffy | 24 | Indore | 10000.00 | +----+----------+-----+-----------+----------+ **ORDERS Table** +-----+---------------------+-------------+--------+ |OID | DATE | CUSTOMER\_ID | AMOUNT | +-----+---------------------+-------------+--------+ | 102 | 2009-10-08 00:00:00 | 3 | 3000 | | 100 | 2009-10-08 00:00:00 | 3 | 1500 | | 101 | 2009-11-20 00:00:00 | 2 | 1560 | | 103 | 2008-05-20 00:00:00 | 4 | 2060 | +-----+---------------------+-------------+--------+

Answer 46

SELECT Customers.ID, Customers.NAME, Customers.AGE, Orders.AMOUNT FROM **CUSTOMERS** INNER JOIN **ORDERS** ON CUSTOMERS.ID = ORDERS.CUSTOMER\_ID;

Question 47

What are the different types of Joins?

Answer 47

**INNER JOIN** - clause allows you to query data from two or more related tables. A comparison operator is required. **LEFT JOIN** - It returns all rows from the left table and the matching rows from the right table. If no matching rows found in the right table, NULL are used. **RIGHT JOIN** - clause starts selecting data from the right table and matching with the rows from the left table. The RIGHT JOIN returns a result set that includes all rows in the right table, whether or not they have matching rows from the left table. **FULL JOIN** - returns a result set that includes rows from both left and right tables. When no matching rows exist for the row in the left table, the columns of the right table will have nulls. Similarly, when no matching rows exist for the row in the right table, the column of the left table will have nulls.