Finals Lecture 2: SQL Flashcards
SQL stands for ___.
Structured Query Language
It is used for storing and managing data in Relational Database Management System (RDBMS).
SQL
What is SQL used for?
It is used for storing and managing data in Relational Database Management System (RDBMS).
RDBMS
Relational Database Management System
It is a standard language for Relational Database System.
SQL
SQL enables user to…
create, read, update and delete relational databases and tables.
All the RDBMS like MySQL, Informix, Oracle, MS Access and SQL Server use ___ as their standard database language.
SQL
SQL allows users to ___ the database in a number of ways, using English-like statements.
query
Structure query language is ___. Generally, keywords of SQL are written in uppercase.
not case sensitive
Statements of SQL are ___. We can use a ___ on one or ___.
dependent on text lines; single SQL statement; multiple text line
Using the ___, you can perform most of the actions in a database.
SQL statements
SQL depends on ___ and ___.
tuple relational calculus; relational algebra
SQL follows the following rules:
- is not case sensitive but is usually written in uppercase
- dependent on text lines
- uses sql statements to perform actions in a db
- depends on tuple relational calculus and relational algebra
When an ___ is executing for any RDBMS, then the system figure out the best way to ___ and the ___ determines that how to interpret the task.
SQL command; carry out the request; SQL engine
In the process, various components are included. These components can be…
optimization Engine, Query engine, Query dispatcher, classic, etc.
All the non-SQL queries are handled by the ___, but SQL query engine won’t ___.
classic query engine; handle logical files
What is the SQL Process
When an SQL command is submitted:
system»_space; figures out the best way to carry out the request
SQL engine» determines how to interpret the task
classic query engine»_space; handles non-SQL queries
SQL query engine»_space; handles SQL queries but not logical files
Graphical framework of SQL Process
SQL Query»_space; Query Language Processor -> [Parser + Optimizer]»_space; DBMS Engine -> [File Manager + Transaction Manager]»_space; Physical Database
What is Advantages of SQL?
- High speed
- No coding needed
- Well defined standards
- Portability
- Interactive language
- Multiple data view
What is SQL Datatype?
SQL Datatype is used to define the values that a column can contain. Every column is required to have a name and data type in the database table
Enlist the SQL datatypes
Binary
Numeric
Extract Numeric
String
Date
SQL commands are ___. It is used to communicate with the database. It is also used to perform specific tasks, functions, and queries of data.
instructions;
It is used to communicate with the database.
SQL commands
It is also used to perform specific tasks, functions, and queries of data.
SQL commands
SQL can perform various tasks like: (5)
create a table, add data to tables, drop the table, modify the table, set permission for user
Types of SQL Commands
DDL, DML, DCL, TCL, DQL
DDL
Data Definition Language
DML
Data Manipulation Language
DCL
Data Control Language
TCL
Transaction Control Language
DQL
Data Query Language
Commands under DDL
Create, Drop, Alter, Truncate
Commands under DML
Insert, Update, Delete
Commands under DCL
Grant, Revoke
Commands under TCL
Commit, Rollback, Save point
Commands under DQL
Select
SQL command that changes the structure of the table like creating a table, deleting a table, altering a table, etc.
DDL, Data Definition Language
DDL command, is used to create a new table in the database.
CREATE
Syntax:
CREATE TABLE TABLE_NAME (COLUMN_NAME DATATYPES[,….]);
Example:
CREATE TABLE EMPLOYEE(Name VARCHAR2(20), Email VARCHAR2(100), DOB DATE);
DDL command, is used to delete both the structure and record stored in the table.
DROP
Syntax:
DROP TABLE ;
Example:
DROP TABLE EMPLOYEE;
DDL command, is used to alter the structure of the database. This change could be either to modify the characteristics of an existing attribute or probably to add a new attribute.
ALTER
Syntax:
ALTER TABLE table_name ADD column_name COLUMN-definition;
ALTER TABLE MODIFY(COLUMN DEFINITION….);
Example:
ALTER TABLE STU_DETAILS ADD(ADDRESS VARCHAR2(20));
ALTER TABLE STU_DETAILS MODIFY (NAME VARCHAR2(20));
DDL command, is used to delete all the rows from the table and free the space containing the table.
TRUNCATE
Syntax:
TRUNCATE TABLE table_name;
Example:
TRUNCATE TABLE EMPLOYEE;
___ are used to modify the database. It is responsible for all form of CHANGES in the database.
DML, Data Manipulation Language
The command of DML is ___ that means it can’t permanently save all the changes in the database. They can be ___.
not auto-committed; rollback
DML command, the ___ is a SQL query. It is used to insert data into the row of a table.
INSERT statement;
INSERT
Syntax:
INSERT INTO TABLE_NAME (col1, col2, col3,…. col N) VALUES (value1, value2, value3, …. valueN);
OR
INSERT INTO TABLE_NAME VALUES (value1, value2, value3, …. valueN);
Example:
INSERT INTO XYZ (Author, Subject) VALUES (“Sonoo”, “DBMS”);
DML command, is used to update or modify the value of a column in the table.
UPDATE
Syntax:
UPDATE table_name SET [column_name1= value1,…column_nameN = valueN] [WHERE CONDITION]
Example:
UPDATE students SET User_Name = ‘Sonoo’ WHERE Student_Id = ‘3’
___ are used to GRANT and TAKE BACK authority from any database user.
DCL commands
DCL command, is used to give user access privileges to a database.
GRANT
Example:
GRANT SELECT, UPDATE ON MY_TABLE TO SOME_USER, ANOTHER_USER;
DCL command, is used to take back permissions from the user.
REVOKE
Example:
REVOKE SELECT, UPDATE ON MY_TABLE FROM USER 1, USER 2;
___ can only use with DML commands like INSERT, DELETE and UPDATE only
TCL commands
These operations are automatically committed in the database that’s why they cannot be used while creating tables or dropping them.
TCL, transaction control language
TCL command, is used to save all the transactions to the database.
COMMIT
Syntax:
COMMIT;
Example:
DELETE FROM CUSTOMERS WHERE AGE = 25;
COMMIT;
TCL command, is used to undo transactions that have not already been saved to the database.
ROLLBACK
Syntax:
ROLLBACK;
Example:
DELETE FROM CUSTOMERS WHERE AGE = 25;
ROLLBACK;
TCL command, is used to roll the transaction back to a certain point without rolling back the entire transaction.
SAVEPOINT
Syntax:
SAVEPOINT SAVEPOINT_NAME;
___ is used to fetch the data from the database.
DQL, data query language
It uses only one command: SELECT
DQL
This is the same as the projection operation of relational algebra. It is used to select the attribute based on the condition described by WHERE clause.
SELECT
Syntax:
SELECT expressions FROM TABLES WHERE conditions;
Example:
SELECT emp_name FROM employee WHERE age > 20
SQL Operators, what are the various types?
Arithmetic Operator, Comparison Operator, Logical Operator
SQL Comparison Operators
+ (adds the value of both operands);
- (is used to subtract the right-hand operand from the left-hand - operand.)
* (used to multiply the value of both operands)
/ (used to divide the left-hand operand by the right-hand operand)
% (used to divide the left-hand operand by the right-hand operand and returns reminder)
SQL Arithmetic Operators
= (checks if two operands values are equal or not, if the values are equal then condition becomes true)
!= (checks if two operands values are equal or not, if values are not equal, then condition becomes true)
<> (checks if two operands values are equal or not, if values are not equal then condition becomes true)
> (checks if the left operand value is greater than right operand value, if yes then condition becomes true)
< (checks if the left operand value is less than right operand value, if yes then condition becomes true)
>= (checks if the left operand value is greater than or equal to the right operand value, if yes then condition becomes true)
<= (checks if the left operand value is less than or equal to the right operand value, if yes then condition becomes true)
!< (checks if the left operand value is not less than the right operand value, if yes then condition becomes true)
!> (checks if the left operand value is not greater than the right operand value, if yes then condition becomes true)
SQL Logical Operators
(1) compares a value to all values in another value set
(2) allows the existence of multiple conditions in an SQL statement
(3) compares the values in the list according to the condition
(4) is used to search for values that are within a set of values
(5) compares a value to that specified list value
(6) reverses the meaning of any logical operator
(7) combines multiple conditions in SQL statements
(8) is used to search for the presence of a row in a specified table
(9) compares a value to similar values using wildcard operator
(1) All
(2) AND
(3) ANY
(4) Between
(5) IN
(6) NOT
(7) OR
(8) EXIST
(9) LIKE
Example:
SQL> CREATE TABLE EMPLOYEE (
EMP_ID INT NOT NULL,
EMP_NAME VARCHAR (25) NOT NULL,
PHONE_NO INT NOT NULL,
ADDRESS CHAR (30),
PRIMARY KEY (ID)
);
- DESC EMPLOYEE;
- DELETE FROM table_name WHERE condition
- DROP TABLE “table_name”;
- SELECT * FROM table_name;
- INSERT INTO TABLE_NAME VALUES (value1, value2, value 3, …. Value N);
- INSERT INTO TABLE_NAME[(col1, col2, col3,…. col N)] VALUES (value1, value2, value 3, …. Value N);
- UPDATE table_name SET column_name = value WHERE condition;
- UPDATE table_name SET column_name = value1, column_name2 = value WHERE condition;
- DELETE FROM table_name WHERE some_condition;
Views in SQL are considered as a ___. A ___ also contains rows and columns.
virtual table; view
To create the view, we can ___ from one or more tables present in the database.
select the fields
A ___ can either have specific rows based on certain condition or all the rows of a table.
view
Creating view
A view can be created using the ___ statement. We can create a view from a ___.
CREATE VIEW; single table or multiple tables
Syntax:
CREATE VIEW view_name AS
SELECT column1, column2…..
FROM table_name
WHERE condition;
Creating View from a single table:
CREATE VIEW DetailsView AS
SELECT NAME, ADDRESS
FROM Student_Details
WHERE STU_ID < 4
Creating View from multiple tables:
In the given example, a view is created named MarksView from two tables Student_Detail and Student_Marks.
CREATE VIEW MarksView AS
SELECT Student_Detail.NAME, Student_Detail.ADDRESS, Student_Marks.MARKS
FROM Student_Detail, Student_Mark
WHERE Student_Detail.NAME = Student_Marks.NAME;
SELECT * FROM MarksView;
DROP VIEW view_name;
View from multiple tables can be created by simply ___.
including multiple tables in the SELECT statement
___ are special lookup tables. It is used to retrieve data from the database very fast.
Indexes
An ___ is used to speed up select queries and where clauses. But it shows down the data input with insert and update statements.
Index
Indexes can be created or dropped without affecting the data.
True | False
True
An index in a database is just like an index in the back of a book.
To create Index statement:
CREATE INDEX index_name ON table_name (column1, column2, …);
Unique Index statement
Syntax:
CREATE UNIQUE INDEX index_name ON table_name (column1, column2, …);
Example:
CREATE UNIQUE INDEX websites_idx ON websites (site_name);
Drop Index Statement
Syntax:
DROP INDEX index_name;
Example:
DROP INDEX websites_idx;
A ___ is a query within another SQL query and embedded within the WHERE clause.
Subquery
A subquery can be placed in a number of SQL clauses like ___ clause, ___ clause, ___ clause
WHERE; FROM; HAVING
You can use Subquery with ___ statements along with the operators like =, <, >, >=, <=, IN, BETWEEN,
SELECT, UPDATE, INSERT, DELETE
A subquery is a…
query within another query
In a subquery…
The outer query is known as the ___, and the inner query is known as a ___.
main query; subquery
Subqueries are on the right side of the ___. A subquery is enclosed in ___.
comparison operator; parentheses
In the Subquery, ___ command cannot be used. But ___ command can be used to perform the same function as ___ command.
ORDER BY; GROUP BY, ORDER BY
SQL subqueries are most frequently used with the ___.
Select statement
Syntax:
SELECT column_name
FROM table_name
WHERE column_name expression operator
( SELECT column_name from table_name WHERE … );
Example:
SELECT *
FROM EMPLOYEE
WHERE ID IN (SELECT ID
FROM EMPLOYEE
WHERE SALARY > 4500)
SQL subquery can also be used with the ___.
In the insert statement, data returned from the subquery is used to___.
Insert statement; insert into another table
In the subquery, the ___ can be modified with any of the character, date functions.
selected data
Syntax:
INSERT INTO table_name (column1, column2, column3….)
SELECT * FROM table_name WHERE VALUE OPERATOR
Example:
INSERT INTO EMPLOYEE_BKP
SELECT * FROM EMPLOYEE
WHERE ID IN (SELECT ID
FROM EMPLOYEE);
Subqueries with the UPDATE Statement
The subquery of SQL can be used in conjunction with the Update statement.
When a subquery is used with the Update statement, then either single or multiple columns in a table can be updated.
Syntax:
UPDATE table SET column_name = new_value WHERE VALUE OPERATOR (SELECT COLUMN_NAME FROM TABLE_NAME WHERE condition);
Example:
Let’s assume we have an EMPLOYEE_BKP table available which is backup of EMPLOYEE table. The given example updates the SALARY by .25 times in the EMPLOYEE table for all employee whose AGE is greater than or equal to 29.
UPDATE EMPLOYEE
SET SALARY = SALARY * 0.25
WHERE AGE IN (SELECT AGE FROM CUSTOMERS_BKP
WHERE AGE >=
Subqueries with the DELETE Statement
Syntax:
DELETE FROM TABLE_NAME WHERE VALUE OPERATOR (SELECT COLUMN_NAME FROM TABLE_NAME WHERE condition);
Example:
Let’s assume we have an EMPLOYEE_BKP table available which is backup of EMPLOYEE table. The given example deletes the records from the EMPLOYEE table for all EMPLOYEE whose AGE is greater than or equal to 29.
DELETE FROM EMPLOYEE
WHERE AGE IN (SELECT AGE FROM EMPLOYEE_BKP
WHERE AGE >= 29
SQL Clauses
Group by Clause
Having Clause
Order by Clause
SQL ___ statement is used to arrange identical data into groups.
GROUP BY
The GROUP BY statement is used with the SQL ___ statement.
SELECT
The ___ statement follows the WHERE clause in a SELECT statement and precedes the ORDER BY clause.
GROUP BY
The GROUP BY statement is used with ___.
aggregation function
SQL GRUOP BY example
Syntax:
SELECT column
FROM table_name
WHERE conditions
GROUP BY column
ORDER BY column
Example:
SELECT COMPANY, COUNT(*)
FROM PRODUCT_MAST
GROUP BY COMPANY;
___ is used to specify a search condition for a group or an aggregate.
HAVING clause
___ is used in a GROUP BY clause. If you are not using GROUP BY clause then you can use HAVING function like a ___ clause
Having; WHERE
Example of using the HAVING clause
Syntax:
SELECT column1, column2 FROM table_name
WHERE conditions
GROUP BY column1, column2
HAVING conditions
ORDER BY column1, column2;
Example:
SELECT COMPANY, COUNT()
FROM PRODUCT_MAST
GROUP BY COMPANY
HAVING COUNT()>2;
The ___ clause sorts the result-set in ascending or descending order.
ORDER BY
It sorts the records in ascending order by default. ___ is used to sort the records in descending order.
ORDER BY; DESC keyword
Example of using the ORDER BY clause
Syntax:
SELECT column1, column2
FROM table_name
WHERE condition
ORDER BY column1, column2… AS C|DESC;
Example:
SELECT *
FROM CUSTOMER
ORDER BY NAME;
OR
SELECT *
FROM CUSTOMER
ORDER BY NAME DESC;
SQL Aggregate Functions
COUNT
SUM
AVG
MAX
MIN
___ is used to Count the number of rows in a database table. It can work on both numeric and non-numeric data types.
COUNT function
COUNT function uses the ___ that returns the count of all the rows in a specified table. ___ considers duplicate and Null.
COUNT(*)
Example of using the COUNT function
Syntax:
COUNT(*) or COUNT( [ALL|DISTINCT] expression )
Example:
SELECT COUNT() FROM PRODUCT_MAST;
SELECT COUNT() FROM PRODUCT_MAST; WHERE RATE>=20;
SELECT COUNT(DISTINCT COMPANY) FROM PRODUCT_MAST;
SELECT COMPANY, COUNT() FROM PRODUCT_MAST GROUP BY COMPANY;
SELECT COMPANY, COUNT() FROM PRODUCT_MAST GROUP BY COMPANY HAVING COUNT(*)>2;
___ is used to calculate the sum of all selected columns. It works on numeric fields only.
Sum function
Syntax and Example of sum function
Syntax:
SUM() or SUM( [ALL|DISTINCT] expression )
Example:
SELECT SUM(COST) FROM PRODUCT_MAST;
SUM() with WHERE:
SELECT SUM(COST) FROM PRODUCT_MAST WHERE QTY>3;
SUM() with GROUP BY:
SELECT SUM(COST) FROM PRODUCT_MAST WHERE QTY>3
GROUP BY COMPANY;
SUM() with HAVING:
SELECT COMPANY, SUM(COST) FROM PRODUCT_MAST GROUP BY COM
PANY HAVING SUM(COST)>=170;
The ___ is used to calculate the average value of the numeric type. AVG function returns the average of all ___.
AVG function; non-Null values
Syntax:
AVG() or AVG( [ALL|DISTINCT] expression )
Example:
SELECT AVG(COST) FROM PRODUCT_MAST;
___ is used to find the maximum value of a certain column. This function determines the largest value of all selected values of a column.
MAX function
Syntax:
MAX() or MAX( [ALL|DISTINCT] expression )
Example:
SELECT MAX(RATE) FROM PRODUCT_MAST;
___ is used to find the minimum value of a certain column. This function determines the smallest value of all selected values of a column
MIN function
Syntax:
MIN() or MIN( [ALL|DISTINCT] expression )
Example:
SELECT MIN(RATE) FROM PRODUCT_MAST
SQL JOIN means ___.
“to combine two or more tables”
In SQL, ___ is used to combine the records from two or more tables in a database.
JOIN clause
Types of SQL JOIN
INNER JOIN
LEFT JOIN
RIGHT JOIN
FULL JOIN
In SQL, ___ selects records that have matching values in both tables as long as the condition is satisfied. It returns the combination of all rows from both the tables where the condition satisfies.
INNER JOIN
Syntax:
SELECT table1.column1, table1.column2, table2.column1,….
FROM table1
INNER JOIN table2
ON table1.matching_column = table2.matching_column;
Example:
SELECT EMPLOYEE.EMP_NAME, PROJECT.DEPARTMENT
FROM EMPLOYEE
INNER JOIN PROJECT
ON PROJECT.EMP_ID = EMPLOYEE.EMP_ID;
The ___ returns all the values from left table and the matching values from the right table. If there is no matching join value, it will return NULL.
SQL left join
Syntax:
SELECT table1.column1, table1.column2, table2.column1,….
FROM table1
LEFT JOIN table2
ON table1.matching_column = table2.matching_column;
Example:
SELECT EMPLOYEE.EMP_NAME, PROJECT.DEPARTMENT
FROM EMPLOYEE
LEFT JOIN PROJECT
ON PROJECT.EMP_ID = EMPLOYEE.EMP_ID;
In SQL, ___ returns all the values from the values from the rows of right table and the matched values from the left table. If there is no matching in both tables, it will return NULL.
RIGHT JOIN
Syntax:
SELECT table1.column1, table1.column2, table2.column1,….
FROM table1
RIGHT JOIN table2
ON table1.matching_column = table2.matching_column;
Example:
SELECT EMPLOYEE.EMP_NAME, PROJECT.DEPARTMENT
FROM EMPLOYEE
RIGHT JOIN PROJECT
ON PROJECT.EMP_ID = EMPLOYEE.EMP_ID;
In SQL, ___ is the result of a combination of both left and right outer join. Join tables have all the records from both tables. It puts NULL on the place of matches not found.
FULL JOIN
Syntax:
SELECT table1.column1, table1.column2, table2.column1,….
FROM table1
FULL JOIN table2
ON table1.matching_column = table2.matching_column;
Example:
SELECT EMPLOYEE.EMP_NAME, PROJECT.DEPARTMENT
FROM EMPLOYEE
FULL JOIN PROJECT
ON PROJECT.EMP_ID = EMPLOYEE.EMP_ID;
The SQL ___ is used to combine the two or more SQL SELECT statements
Set operation
Types of Set Operation
Union
UnionAll
Intersect
Minus
The ___ is used to combine the result of two or more SQL SELECT queries.
SQL Union operation
In the union operation, all the ___ must be same in both the tables on which UNION operation is being applied.
number of datatype and columns
The union operation eliminates the ___ from its result set.
duplicate rows
Syntax and Example of using the Union Operation
Syntax:
SELECT column_name FROM table1
UNION
SELECT column_name FROM table2;
Example:
SELECT * FROM First
UNION
SELECT * FROM Second
It is used to combine two SELECT statements.
Intersect operation
The ___ returns the common rows from both the SELECT statements.
Intersect operation
In the ___, the number of datatype and columns must be the same. It has no duplicates and it arranges the data in ascending order by default.
Intersect operation
Syntax and Example of Intersect operation
Syntax:
SELECT column_name FROM table1
INTERSECT
SELECT column_name FROM table2;
Example:
SELECT * FROM First
INTERSECT
SELECT * FROM Second;
It combines the result of two SELECT statements. ___ is used to display the rows which are present in the first query but absent in the second query.
It has no duplicates and data arranged in ascending order by default.
Minus operator
Syntax and Example of Minus Operation
Syntax:
SELECT column_name FROM table1
MINUS
SELECT column_name FROM table2;
Example:
SELECT * FROM First
MINUS
SELECT * FROM Second
