Database/SQL Flashcards
A _________ is a collection of information that is organized so that it can be easily accessed, managed and updated.
database
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.
relational
Often, data in a ________ database is organized into tables.
relational
Tables can have hundreds, thousands, sometimes even millions of rows of data. These rows are often called ______.
records
_______ are labeled with a descriptive name (say, age for example) and have a specific _____ _____.
Columns are labeled with a descriptive name (say, age for example) and have a specific data type.
A ______ _______ ______ ______ (_____) is a program that allows you to create, update, and administer a relational database
relational database management system (RDBMS)
What does SQL stand for?
Structured Query Language
___ (________ _____ ________) is a programming language used to communicate with data stored in a relational database management system
SQL (Structured Query Language)
SQLite is a relational database management system
True or False
True
_____ contains a minimal set of SQL commands (which are the same across all RDBMSs). Other RDBMSs may use other variants.
SQLight
Why do we need SQL?
1.
2.
3.
4.
5.
6.
7.
- 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.
Describe a simple SQL Architecture
A _____ ___ is a field in a table which uniquely identifies each row/record in a database table.
primary key
Primary keys must contain ______ ______.
Unique Values
A primary key column can have NULL values.
True or False
False
A primary key cannot have NULL values
A table can have only one primary key
True or False
True
A primary key can only consist of a single field.
True or False
False
A primary key may consist of a single or multiple keys.
When multiple fields are used as a primary key, they are called a ________ ___.
composite key
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
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).
Generally, the primary key is created while creating the database and the table.
True or False
True
The primary key can’t be created after the creation of the table as shown below.
True or False
False
The primary key can also be created after the creation of the table as shown below.
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)
);
ALTER TABLE CUSTOMER ADD PRIMARY KEY (ID);
A _____ ___ is a key used to link two tables together.
foreign key
A foriegn key is sometimes called a _______ ___
referencing key
A Foreign Key is a column or a combination of columns whose values match a ______ ____ in a different table.
Primary Key
The relationship between 2 tables matches the ______ ___ in one of the tables with a ______ ___ in the second table.
The relationship between 2 tables matches the Primary Key in one of the tables with a Foreign Key in the second table.
Consider the following two tables, “Customers” and “Orders”. Once the tables have been created, what syntax could create the foreign key?
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)
);
ALTER TABLE ORDERS
ADD FOREIGN KEY (Customer_ID) REFERENCES CUSTOMERS (ID);
What does DDL mean?
Data Definition Language
What are the 3 main commands of DDL?
CREATE, ALTER, DROP
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.
What does DML Mean
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.
What are the commands of DQL
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.
What does DCL mean?
Data Control Language
What are the commands of DCL?
GRANT - REVOKE
GRANT - gives user’s access privileges to database.
REVOKE - withdraw user’s access privileges given by using the GRANT command.
What does TCL mean?
Transaction Control Language
What are the 3 TCL main statements/commands?
SAVEPOINT, ROLLBACK, COMMIT
COMMIT – commits a Transaction.
ROLLBACK – rollbacks a transaction in case of any error occurs.
SAVEPOINT –sets a savepoint within a transaction.
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 __________.
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;
What syntax would you use to fetch all the fields available in the field?
SELECT * FROM table_name;
Consider the following “Customers” table. Select only the ID, NAME and SALARY columns.
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 |
+—-+———-+—–+———–+———-+
SELECT ID, NAME, SALARY FROM CUSTOMERS;
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.
WHERE
You should use the _____ clause to filter the records and fetching only the necessary records.
WHERE
The WHERE clause is only used in the SELECT statement.
True or False
FALSE
The WHERE clause can be used in the SELECT, UPDATE & DELETE statement.
Using the “Customers” table retrieve all the names of customers who’s age is greater than 20.
+—-+———-+—–+———–+———-+
+—-+———-+—–+———–+———-+
| 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 |
+—-+———-+—–+———–+———-+
ID | NAME | AGE | ADDRESS | SALARY |
SELECT CUSTOMERS.AGE from CUSTOMERS where AGE > 20;
NOTE:Can use DB name as prefix before column name.
The SQL ___________ Statement is used to add new rows of data to a table in the database.
INSERT INTO
Use an INSERT INTO statement to add data into each column of the following table.
+—-+———-+—–+———–+———-+
+—-+———-+—–+———–+———-+
| 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 |
+—-+———-+—–+———–+———-+
ID | NAME | AGE | ADDRESS | SALARY |
INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (1, ‘Ramesh’, 32, ‘Ahmedabad’, 2000.00 );
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.
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.
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
+—-+———-+—–+———–+———-+
+—-+———-+—–+———–+———-+
| 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 |
+—–+———————+————-+——–+
ID | NAME | AGE | ADDRESS | SALARY |
SELECT Customers.ID, Customers.NAME, Customers.AGE, Orders.AMOUNT
FROM CUSTOMERS
INNER JOIN ORDERS ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID;
What are the different types of Joins?
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.
