Finals Lecture 2: SQL Flashcards
1
Q
SQL stands for ___.
A
Structured Query Language
2
Q
It is used for storing and managing data in Relational Database Management System (RDBMS).
A
SQL
3
Q
What is SQL used for?
A
It is used for storing and managing data in Relational Database Management System (RDBMS).
4
Q
RDBMS
A
Relational Database Management System
5
Q
It is a standard language for Relational Database System.
A
SQL
6
Q
SQL enables user to…
A
create, read, update and delete relational databases and tables.
7
Q
All the RDBMS like MySQL, Informix, Oracle, MS Access and SQL Server use ___ as their standard database language.
A
SQL
8
Q
SQL allows users to ___ the database in a number of ways, using English-like statements.
A
query
9
Q
Structure query language is ___. Generally, keywords of SQL are written in uppercase.
A
not case sensitive
10
Q
Statements of SQL are ___. We can use a ___ on one or ___.
A
dependent on text lines; single SQL statement; multiple text line
11
Q
Using the ___, you can perform most of the actions in a database.
A
SQL statements
12
Q
SQL depends on ___ and ___.
A
tuple relational calculus; relational algebra
13
Q
SQL follows the following rules:
A
- 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
14
Q
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.
A
SQL command; carry out the request; SQL engine
15
Q
In the process, various components are included. These components can be…
A
optimization Engine, Query engine, Query dispatcher, classic, etc.
16
Q
All the non-SQL queries are handled by the ___, but SQL query engine won’t ___.
A
classic query engine; handle logical files
17
Q
What is the SQL Process
A
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
18
Q
Graphical framework of SQL Process
A
SQL Query»_space; Query Language Processor -> [Parser + Optimizer]»_space; DBMS Engine -> [File Manager + Transaction Manager]»_space; Physical Database
19
Q
What is Advantages of SQL?
A
- High speed
- No coding needed
- Well defined standards
- Portability
- Interactive language
- Multiple data view
20
Q
What is SQL Datatype?
A
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
21
Q
Enlist the SQL datatypes
A
Binary
Numeric
Extract Numeric
String
Date
22
Q
SQL commands are ___. It is used to communicate with the database. It is also used to perform specific tasks, functions, and queries of data.
A
instructions;
23
Q
It is used to communicate with the database.
A
SQL commands
24
Q
It is also used to perform specific tasks, functions, and queries of data.
A
SQL commands
25
SQL can perform various tasks like: (5)
create a table, add data to tables, drop the table, modify the table, set permission for user
26
Types of SQL Commands
DDL, DML, DCL, TCL, DQL
27
DDL
Data Definition Language
28
DML
Data Manipulation Language
29
DCL
Data Control Language
30
TCL
Transaction Control Language
31
DQL
Data Query Language
32
Commands under DDL
Create, Drop, Alter, Truncate
33
Commands under DML
Insert, Update, Delete
34
Commands under DCL
Grant, Revoke
35
Commands under TCL
Commit, Rollback, Save point
36
Commands under DQL
Select
37
SQL command that changes the structure of the table like creating a table, deleting a table, altering a table, etc.
DDL, Data Definition Language
38
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);
39
DDL command, is used to delete both the structure and record stored in the table.
DROP
Syntax:
DROP TABLE ;
Example:
DROP TABLE EMPLOYEE;
40
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));
41
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;
42
___ are used to modify the database. It is responsible for all form of CHANGES in the database.
DML, Data Manipulation Language
43
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
44
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");
45
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'
46
___ are used to GRANT and TAKE BACK authority from any database user.
DCL commands
47
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;
48
DCL command, is used to take back permissions from the user.
REVOKE
Example:
REVOKE SELECT, UPDATE ON MY_TABLE FROM USER 1, USER 2;
49
___ can only use with DML commands like INSERT, DELETE and UPDATE only
TCL commands
50
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
51
TCL command, is used to save all the transactions to the database.
COMMIT
Syntax:
COMMIT;
Example:
DELETE FROM CUSTOMERS WHERE AGE = 25;
COMMIT;
52
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;
53
TCL command, is used to roll the transaction back to a certain point without rolling back the entire transaction.
SAVEPOINT
Syntax:
SAVEPOINT SAVEPOINT_NAME;
54
___ is used to fetch the data from the database.
DQL, data query language
55
It uses only one command: SELECT
DQL
56
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
57
SQL Operators, what are the various types?
Arithmetic Operator, Comparison Operator, Logical Operator
58
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)
59
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)
60
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
61
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;
62
Views in SQL are considered as a ___. A ___ also contains rows and columns.
virtual table; view
63
To create the view, we can ___ from one or more tables present in the database.
select the fields
64
A ___ can either have specific rows based on certain condition or all the rows of a table.
view
65
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;
66
Creating View from a single table:
CREATE VIEW DetailsView AS
SELECT NAME, ADDRESS
FROM Student_Details
WHERE STU_ID < 4
67
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;
68
View from multiple tables can be created by simply ___.
including multiple tables in the SELECT statement
69
___ are special lookup tables. It is used to retrieve data from the database very fast.
Indexes
70
An ___ is used to speed up select queries and where clauses. But it shows down the data input with insert and update statements.
Index
71
Indexes can be created or dropped without affecting the data.
True | False
True
72
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, ...);
73
Unique Index statement
Syntax:
CREATE UNIQUE INDEX index_name ON table_name (column1, column2, ...);
Example:
CREATE UNIQUE INDEX websites_idx ON websites (site_name);
74
Drop Index Statement
Syntax:
DROP INDEX index_name;
Example:
DROP INDEX websites_idx;
75
A ___ is a query within another SQL query and embedded within the WHERE clause.
Subquery
76
A subquery can be placed in a number of SQL clauses like ___ clause, ___ clause, ___ clause
WHERE; FROM; HAVING
77
You can use Subquery with ___ statements along with the operators like =, <, >, >=, <=, IN, BETWEEN,
SELECT, UPDATE, INSERT, DELETE
78
A subquery is a...
query within another query
79
In a subquery...
The outer query is known as the ___, and the inner query is known as a ___.
main query; subquery
80
Subqueries are on the right side of the ___. A subquery is enclosed in ___.
comparison operator; parentheses
81
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
82
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)
83
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
84
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);
85
Subqueries with the UPDATE Statement
The subquery of SQL can be used in conjunction with the Update statement.
86
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 >=
87
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
88
SQL Clauses
Group by Clause
Having Clause
Order by Clause
89
SQL ___ statement is used to arrange identical data into groups.
GROUP BY
90
The GROUP BY statement is used with the SQL ___ statement.
SELECT
91
The ___ statement follows the WHERE clause in a SELECT statement and precedes the ORDER BY clause.
GROUP BY
92
The GROUP BY statement is used with ___.
aggregation function
93
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;
94
___ is used to specify a search condition for a group or an aggregate.
HAVING clause
95
___ 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
96
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;
97
The ___ clause sorts the result-set in ascending or descending order.
ORDER BY
98
It sorts the records in ascending order by default. ___ is used to sort the records in descending order.
ORDER BY; DESC keyword
99
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;
100
SQL Aggregate Functions
COUNT
SUM
AVG
MAX
MIN
101
___ 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
102
COUNT function uses the ___ that returns the count of all the rows in a specified table. ___ considers duplicate and Null.
COUNT(*)
103
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;
104
___ is used to calculate the sum of all selected columns. It works on numeric fields only.
Sum function
105
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;
106
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;
107
___ 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;
108
___ 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
109
SQL JOIN means ___.
"to combine two or more tables"
110
In SQL, ___ is used to combine the records from two or more tables in a database.
JOIN clause
111
Types of SQL JOIN
INNER JOIN
LEFT JOIN
RIGHT JOIN
FULL JOIN
112
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;
113
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;
114
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;
115
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;
116
The SQL ___ is used to combine the two or more SQL SELECT statements
Set operation
117
Types of Set Operation
Union
UnionAll
Intersect
Minus
118
The ___ is used to combine the result of two or more SQL SELECT queries.
SQL Union operation
119
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
120
The union operation eliminates the ___ from its result set.
duplicate rows
121
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
122
It is used to combine two SELECT statements.
Intersect operation
123
The ___ returns the common rows from both the SELECT statements.
Intersect operation
124
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
125
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;
126
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
127
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
