SQL_1

Question 1

DELETE command

Answer 1

will delete everything unless given a where condition, can use the rollback command to undo deletions.

Question 2

TRUNCATE command

Answer 2

deletes all rows but leaves table structure in place, cannot be undone with rollback command

Question 3

DROP command

Answer 3

drops the entire table structure along with all rows/contents, cannot be undone with rollback command

Question 4

PRIMARY KEY

Answer 4

constraint can be used to uniquely identify the row.

Question 5

UNIQUE

Answer 5

columns have a different value for every row.

Question 6

NOT NULL

Answer 6

columns must have a value.

Question 7

DEFAULT

Answer 7

assigns a default value for the column when no value is specified.

Question 8

INSERT INTO

Answer 8

statement is used to add a new record (row) to a table.

Question 9

UPDATE

Answer 9

statement is used to edit records (rows) in a table. It includes a SET clause that indicates the column to edit and a WHERE clause for specifying the record(s).

Question 10

% Wildcard

Answer 10

The % wildcard can be used in a LIKE operator pattern to match zero or more unspecified character(s).

Question 11

_ Wildcard

Answer 11

The _ wildcard can be used in a LIKE operator pattern to match any single unspecified character.

Question 12

HAVING

Answer 12

The HAVING clause is used to further filter the result set groups provided by the GROUP BY clause. HAVING is often used with aggregate functions to filter the result set groups based on an aggregate property.

Question 13

ROUND()

Answer 13

he ROUND() function will round a number value to a specified number of places. It takes two arguments: a number, and a number of decimal places. It can be combined with other aggregate functions, as shown in the given query.

Question 14

Outer Join

Answer 14

An outer join will combine rows from different tables even if the join condition is not met. In a LEFT JOIN, every row in the left table is returned in the result set, and if the join condition is not met, then NULL values are used to fill in the columns from the right table. Left, right, and full joins are all outer joins.

Question 15

Cross Join

Answer 15

The CROSS JOIN clause is used to combine each row from one table with each row from another in the result set. This JOIN is helpful for creating all possible combinations for the records (rows) in two tables.

Question 16

Inner Join

Answer 16

The JOIN clause allows for the return of results from more than one table by joining them together with other results based on common column values specified using an ON clause. INNER JOIN is the default JOIN and it will only return results matching the condition specified by ON.

Question 17

Left join

Answer 17

An outer join that returns all rows from the left table, and the matched rows from the right table

Question 18

Right join

Answer 18

An outer join that returns all rows from the right table, and the matched rows from the left table

Question 19

Window Function

Answer 19

Similar to an aggregate function, a window function calculates on a set of rows. However, a window function does not cause rows to become grouped into a single output row.

Question 20

PARTITION BY

Answer 20

the PARTITION BY clause is a subclause of the OVER clause. The PARTITION BY clause divides a query’s result set into partitions. The window function is operated on each partition separately and recalculate for each partition.

Question 21

UNION vs JOIN

Answer 21

The UNION operator combines result sets of two or more SELECT statements into a single result set (no duplicates). A JOIN similarly combines tables but does so using common values using ON statements, where as the union operator just adds the select statements together.

Question 22

UNION ALL

Answer 22

A UNION statement that includes all data from both SELECT statements including duplicates.

Question 23

CTE / WITH Clause

Answer 23

A Common Table Expression is a named temporary result set. You create a CTE using a WITH query, then reference it within a SELECT, INSERT, UPDATE, or DELETE statement. If you use a CTE, you don’t need to create and drop a table. You can simply reference the temporary result set created by the WITH query

Question 24

INTERSECT Statement

Answer 24

The SQL INTERSECT clause/operator is used to combine two SELECT statements, but returns rows only from the first SELECT statement that are identical to a row in the second SELECT statement. This means INTERSECT returns only common rows returned by the two SELECT statements.

Just as with the UNION operator, the same rules apply when using the INTERSECT operator. MySQL does not support the INTERSECT operator.

Question 25

CASE Statement

Answer 25

The CASE statement goes through conditions and returns a value when the first condition is met (like an if-then-else statement). So, once a condition is true, it will stop reading and return the result. If no conditions are true, it returns the value in the ELSE clause. If there is no ELSE part and no conditions are true, it returns NULL. CASE Syntax - ``` CASE WHEN condition1 THEN result1 WHEN condition2 THEN result2 WHEN conditionN THEN resultN ELSE result END; ```

Question 26

Subquery

Answer 26

A subquery is a SQL query nested inside a larger query.

Question 27

Index

Answer 27

Indexes are special lookup tables that the database search engine can use to speed up data retrieval. Simply put, an index is a pointer to data in a table. An index in a database is very similar to an index in the back of a book.

Question 28

Clustered Index vs Heap (Non-Clustered Index)

Answer 28

Clustered index provides an innate ordering for the table it is defined on and follows whatever column order the index is defined on. In a clustered index, when rows are inserted, updated, or deleted, the underlying order of data is retained. A heap is a table that is stored without any underlying order. When rows are inserted into a heap, there is no way to ensure where the pages will be written nor are those pages guaranteed to remain in the same order as the table is written to or when maintenance is performed against it.

Question 29

Constraints

Answer 29

SQL constraints are used to specify rules for the data in a table. The following constraints are commonly used in SQL: NOT NULL - Ensures that a column cannot have a NULL value UNIQUE - Ensures that all values in a column are different PRIMARY KEY - A combination of a NOT NULL and UNIQUE. Uniquely identifies each row in a table FOREIGN KEY - Prevents actions that would destroy links between tables CHECK - Ensures that the values in a column satisfies a specific condition DEFAULT - Sets a default value for a column if no value is specified CREATE INDEX - Used to create and retrieve data from the database very quickly

Question 30

field vs record

Answer 30

a field is a column name a record is the same as a row

Question 31

Subsets of SQL

Answer 31

DDL - Data Definition Language actually consists of the SQL commands that can be used to define the database schema. It deals with descriptions of the database schema and is used to create and modify the structure of database objects in the database. DML - Data Manipulation Language handles user requests. These commands are used for manipulating the data in database. DCL - Data Control Language includes commands such as GRANT and REVOKE which mainly deals with the rights, permissions, and other controls of the database system. It is used to retrieve the stored or saved data. TCL - Transaction Control Language commands deal with the transaction within the database.They are also used to make the changes that are done by DML statements.

Question 32

DATE_TRUNC

Answer 32

zeros out everything after the argument but keeps date type structure

Question 33

TO_CHAR

Answer 33

makes dates prettier but converts them into a string instead of date type structure

Question 34

CURRENT_TIMESTAMP

Answer 34

gets timestamp, similar to GET_DATE()

Question 35

Database Normalization vs Denormalization

Answer 35

Normalization is used to remove redundant data from the database and to store non-redundant and consistent data into it. Denormalization is used to combine multiple table data into one so that it can be queried quickly.

Question 36

RDBMS

Answer 36

Relational Database Management System - A database management system (or DBMS) is essentially nothing more than a computerized data-keeping system. MySQL and PostgreSQL are RDBMS'.

Question 37

Write a SQL query to display the current date

Answer 37

SELECT CURRENT_DATE;

Question 38

ACID Properties

Answer 38

Atomicity - By this, we mean that either the entire transaction takes place at once or doesn’t happen at all. There is no midway i.e. transactions do not occur partially. Each transaction is considered as one unit and either runs to completion or is not executed at all. It involves the following two operations. —Abort: If a transaction aborts, changes made to database are not visible. —Commit: If a transaction commits, changes made are visible. Atomicity is also known as the ‘All or nothing rule’.

Question 39

Trigger statements

Answer 39

Trigger is a statement that a system executes automatically when there is any modification to the database. In a trigger, we first specify when the trigger is to be executed and then the action to be performed when the trigger executes. Triggers are used to specify certain integrity constraints and referential constraints that cannot be specified using the constraint mechanism of SQL. Example – Suppose, we are adding a tupple to the ‘Donors’ table that is some person has donated blood. So, we can design a trigger that will automatically add the value of donated blood to the ‘Blood_record’ table. Types of Triggers – We can define 6 types of triggers for each table: AFTER INSERT activated after data is inserted into the table. AFTER UPDATE: activated after data in the table is modified. AFTER DELETE: activated after data is deleted/removed from the table. BEFORE INSERT: activated before data is inserted into the table. BEFORE UPDATE: activated before data in the table is modified. BEFORE DELETE: activated before data is deleted/removed from the table.

Question 40

SQL Operators

Answer 40

SQL Arithmetic Operators ``` + Add - Subtract * Multiply / Divide % Modulo ``` SQL Bitwise Operators & Bitwise AND | Bitwise OR ^ Bitwise exclusive OR SQL Comparison Operators ``` = Equal to > Greater than < Less than >= Greater than or equal to <= Less than or equal to <> Not equal to ``` SQL Compound Operators ``` += Add equals -= Subtract equals *= Multiply equals /= Divide equals %= Modulo equals &= Bitwise AND equals ^-= Bitwise exclusive equals |*= Bitwise OR equals ``` SQL Logical Operators ALL TRUE if all of the subquery values meet the condition AND TRUE if all the conditions separated by AND is TRUE ANY TRUE if any of the subquery values meet the condition BETWEEN TRUE if the operand is within the range of comparisons EXISTS TRUE if the subquery returns one or more records IN TRUE if the operand is equal to one of a list of expressions LIKE TRUE if the operand matches a pattern NOT Displays a record if the condition(s) is NOT TRUE OR TRUE if any of the conditions separated by OR is TRUE SOME TRUE if any of the subquery values meet the condition

Question 41

NULL values same as zero or blank space?

Answer 41

No

Question 42

Wildcards

Answer 42

Symbol Description Example % Represents zero or more characters bl% finds bl, black, blue, and blob _ Represents a single character h_t finds hot, hat, and hit [] Represents any single character within the brackets h[oa]t finds hot and hat, but not hit ^ Represents any character not in the brackets h[^oa]t finds hit, but not hot and hat - Represents a range of characters c[a-b]t finds cat and cbt All the wildcards can also be used in combinations! Here are some examples showing different LIKE operators with '%' and '_' wildcards: LIKE Operator Description WHERE CustomerName LIKE 'a%' Finds any values that starts with "a" WHERE CustomerName LIKE '%a' Finds any values that ends with "a" WHERE CustomerName LIKE '%or%' Finds any values that have "or" in any position WHERE CustomerName LIKE '_r%' Finds any values that have "r" in the second position WHERE CustomerName LIKE 'a__%' Finds any values that starts with "a" and are at least 3 characters in length WHERE ContactName LIKE 'a%o' Finds any values that starts with "a" and ends with "o"

Question 43

Group Functions

Answer 43

Group functions are built-in SQL functions that operate on groups of rows and return one value for the entire group. These functions are: COUNT, MAX, MIN, AVG, SUM, DISTINCT

Question 44

3 Types of Table Relationships

Answer 44

a. One-One Relationship (1-1 Relationship) One-to-One (1-1) relationship is defined as the relationship between two tables where both the tables should be associated with each other based on only one matching row. b. One-Many Relationship (1-M Relationship) The One-to-Many relationship is defined as a relationship between two tables where a row from one table can have multiple matching rows in another table. c. Many-Many Relationship (M-M Relationship) A row from one table can have multiple matching rows in another table, and a row in the other table can also have multiple matching rows in the first table this relationship is defined as a many to many relationship.

Question 45

IN vs BETWEEN

Answer 45

IN operator allows you to easily test if the expression matches any value in the list of values. It is used to remove the need of multiple OR condition in SELECT, INSERT, UPDATE or DELETE. The SQL BETWEEN condition allows you to easily test if an expression is within a range of values (inclusive). The values can be text, date, or numbers.

Question 46

MERGE Statement

Answer 46

The MERGE command in SQL is actually a combination of three SQL statements: INSERT, UPDATE and DELETE. In simple words, the MERGE statement in SQL provides a convenient way to perform all these three operations together which can be very helpful when it comes to handle the large running databases. But unlike INSERT, UPDATE and DELETE statements MERGE statement requires a source table to perform these operations on the required table which is called as target table.

Question 47

SQL Clauses

Answer 47

Clauses are in-built functions available to us in SQL. With the help of clauses, we can deal with data easily stored in the table. Clauses help us filter and analyze data quickly. When we have large amounts of data stored in the database, we use Clauses to query and get data required by the user. ``` WHERE Clause ORDER BY clause HAVING Clause TOP Clause GROUP BY Clause ```

Question 48

WHERE vs HAVING

Answer 48

In simple words, the WHERE and HAVING clauses act as filters; they remove records or data that don’t meet certain criteria from the final result of a query. However, they are applied to different sets of data. That’s the important point to understand about WHERE vs. HAVING: WHERE filters at the record level, while HAVING filters at the "group of records" level.

Question 49

SQL Aggregate Functions

Answer 49

AVG() – returns the average of a set. COUNT() – returns the number of items in a set. MAX() – returns the maximum value in a set. MIN() – returns the minimum value in a set SUM() – returns the sum of all or distinct values in a set

Question 50

What is a view?

Answer 50

To put it simply, a view is a stored SQL query. Every time a view is used, it executes its stored query and creates a result set consisting of rows and columns. An SQL view is called a virtual table because it does not store the rows and columns on the disk like a concrete table. Instead, it just contains the SQL query. Let’s look at the diagram below to get a better grasp of what a view is.

Question 51

Stored Procedure

Answer 51

A stored procedure is a prepared SQL code that you can save, so the code can be reused over and over again. So if you have an SQL query that you write over and over again, save it as a stored procedure, and then just call it to execute it.

Question 52

COMMIT and ROLLBACK

Answer 52

COMMIT and ROLLBACK are performed on transactions. A transaction is the smallest unit of work that is performed against a database. Its a sequence of instructions in a logical order. A transaction can be performed manually by a programmer or it can be triggered using an automated program. COMMIT is the SQL command that is used for storing changes performed by a transaction. When a COMMIT command is issued it saves all the changes since last COMMIT or ROLLBACK. ROLLBACK is the SQL command that is used for reverting changes performed by a transaction. When a ROLLBACK command is issued it reverts all the changes since last COMMIT or ROLLBACK.