Structured Query Language Flashcards by Beatriz de Sousa

What are the four key characteristics of SQL?

Set-oriented and declarative;
Free-form language;
Case insensitive;
Can be used both interactively from a command prompt or executed by a program.

How well did you know this?

Not at all

Perfectly

Code for check existing databases:

SHOW DATABASES;

How well did you know this?

Not at all

Perfectly

Code for creating a database:

CREATE DATABASE [IF NOT EXISTS] database_name
[CHARACTER SET charset_name]
[COLLATE collation_name]

How well did you know this?

Not at all

Perfectly

Code for use a database:

USE database_name;

How well did you know this?

Not at all

Perfectly

Code for drop a database:

DROP DATABASE [IF EXISTS] database_name

How well did you know this?

Not at all

Perfectly

Code for see the tables present in the database:

SHOW TABLES;
(after Use command)

How well did you know this?

Not at all

Perfectly

Code for creating a table:

CREATE TABLE [IF NOT EXISTS] table_name(
column_name data_type(length) [NOT NULL] [DEFAULT value]
[AUTO_INCREMENT]
col2 data_type(length) [NOT NULL] [DEFAULT value] [AUTO_INCREMENT]
col3 data_type(length) [NOT NULL] [DEFAULT value] [AUTO_INCREMENT]
) ENGINE = storage_engine;

How well did you know this?

Not at all

Perfectly

In the following code what does NOT NULL does?

It indicates that the column does not allow NULL

How well did you know this?

Not at all

Perfectly

In the following code what does DEFAULT does?

The default value is used to specify the default value of the column.

How well did you know this?

Not at all

Perfectly

In the following code what does AUTO INCREMENT does?

The auto increment indicates that the value of the column is incremented by one automatically whenever a new row is inserted into the table. Each table has one and only one AUTO_INCREMENT column.

How well did you know this?

Not at all

Perfectly

In the following code what does ENGINE does?

We can optionally specify the storage engine for the table in the ENGINE clause. We can use any storage engine such as InnoDB and MyISAM. If we don’t explicitly declare the storage engine, MySQL will use InnoDB by default.

How well did you know this?

Not at all

Perfectly

What are examples of datatypes?

CHAR (size)
VARCHAR (size)
TEXT
INT (size)
FLOAT (size, d): small number with a floating decimal point
DOUBLE (size, d): large number with a floating decimal point
DATE()
YEAR()

How well did you know this?

Not at all

Perfectly

Code to create primary keys:

CREATE TABLE [IF NOT EXISTS] table_name(
col1 data_type(length)[PRIMARY KEY]
col2 data_type(length)[PRIMARY KEY]
col3 data_type(length)[PRIMARY KEY]
);

CREATE TABLE [IF NOT EXISTS] table_name(
col1 data_type(length)
col2 data_type(length)
col3 data_type(length)
PRIMARY KEY(col1, col2, …)
);

How well did you know this?

Not at all

Perfectly

Code to create foreign keys:

CREATE TABLE [IF NOT EXISTS] table_name(
col1 data_type(length)
PRIMARY KEY(col1, col2, …)
CONSTRAINT constraint_name
FOREIGN KEY (columns)
REFERENCES parent_table (columns)
ON DELETE action
ON UPDATE action
);

How well did you know this?

Not at all

Perfectly

What does the Constraint clause do in the following code:

Allows us to define the constraint name for the foreign key constraint. If we omit it, MySQL will generate a name automatically.

How well did you know this?

Not at all

Perfectly

What does the Foreign key clause do in the following code:

Specifies the columns in the child table that refer to primary key columns in the parent table.

How well did you know this?

Not at all

Perfectly

What does the References clause do in the following code:

Specifies the parent table and its columns to which the columns in the child table refer. The number of columns in the child table and parent table specified in the foreign key and references must be the same.

How well did you know this?

Not at all

Perfectly

What does the On delete clause do in the following code:

Allows us to define what happens to the records in the child table when the records in the parent table are deleted. If we omit the on delete clause and delete a record in the parent table that has records in the child table refer to, MySQL will reject the deletion. Possible actions: CASCADE, SET NULL, NO ACTION.

How well did you know this?

Not at all

Perfectly

What does the On update clause do in the following code:

Enables us to define what happens to the rows in the child table when the records in the parent table are updated. We can omit the on update clause to let MySQL reject any updates to the rows in the child table when the rows in the parent table are updated. Possible actions: CASCADE, SET NULL, NO ACTION.

How well did you know this?

Not at all

Perfectly

Code to drop a table:

DROP TABLE [IF EXISTS] table_name[, table_name] …

How well did you know this?

Not at all

Perfectly

What does the if exists addition do in the following code?

DROP TABLE [IF EXISTS] table_name[, table_name] …

It helps to prevent from the attempt of removing non-existent tables. When we use this addition, MySQL generates a NOTE, which can be retrieved by using the SHOW WARNING statement. It is important to note that this statement removes all existing tables and issues an error message or a note when we have a non-existent table in the list.

How well did you know this?

Not at all

Perfectly

What are the CRUD operations?

Create (insert)
Read
Update
Delete

How well did you know this?

Not at all

Perfectly

Code for insert data into table:

INSERT INTO table_name(c1,c2,…)
VALUES (v1, v2, …)

How well did you know this?

Not at all

Perfectly

Code for read table:

SELECT
column_1, column_2, …
FROM
table_1
[INNER | LEFT | RIGHT] JOIN table_2 ON conditions
WHERE
conditions
GROUP BY column_1
HAVING group_conditions
ORDER BY column_1
LIMIT offset, length;

How well did you know this?

Not at all

Perfectly

Code for update a table:

UPDATE [LOW_PRIORITY][IGNORE] table_name SET column_name1 = expr1, column_name2 = expr2, [WHERE condition];

Code for delete data from a table:

DELETE FROM table_name WHERE condition;

Describe the limit clause and how to use it.

The limit clause is used in the SELECT statement to constrain the number of rows to return. Code: SELECT select_list FROM table_name LIMIT [offset,] row_count;

What does the offset do in the following code? SELECT select_list FROM table_name LIMIT [offset,] row_count;

The offset specifies the offset of the first row to return. The offset of the first row is 0 and not 1.

What does the row_count do in the following code? SELECT select_list FROM table_name LIMIT [offset,] row_count;

The row_count specifies the maximum number of rows to return.

What does the distinct clause do?

Is to remove duplicates and it is used with select. Example: SELECT DISTINCT e.first_name FROM employees AS e LIMIT 10;

What does the between operator do?

Is a logical operator that allows us to specify whether a value is within a range or not. Example: SELECT e.emp_no, e.first_name, e.last_name, e.hire_date FROM employees AS e WHERE e.hire_date BETWEEN '1999-01-01' AND '199-12-31'

What is the like operator?

Is a logical operator that tests whether a string contains a specified pattern or not. Example: retrieve the employees whose last names begin with 'T': SELECT e.first_name, e.last_name FROM employees AS e WHERE e.last_name LIKE 'T%'

In the following code what is '%' SELECT e.first_name, e.last_name FROM employees AS e WHERE e.last_name LIKE 'T%'

Is a wildcard. Represents zero or more characters Example: T% finds Tires, Teodoro, Timóteo

In the following code what is '_' SELECT e.first_name, e.last_name FROM employees AS e WHERE e.last_name LIKE 'T%'

Is a wildcard. Represents a single character. Example: h_t finds hot, hat, and hit

Is there a not like operator?

Yes. Example: retrieve the employees where the last name does not begin with 'Tr': SELECT e.first_name, e.last_name FROM employees AS e WHERE e.last_name NOT LIKE 'Tr%';

What is the code for MySQL alias for columns?

SELECT [column_1 | expression] AS descriptive_name FROM table_name;

What is the code for MySQL alias for tables?

table_name AS table_alias

Is the AS keyword optional?

For tables, we can omit it, but for columns no.

What does the in operator do?

It allows to determine if a specified value matches any value in a set of values or returned by a subquery.

What is the code for usage of in operator?

SELECT column1, column2, … FROM table_name WHERE column_n IN (value1, valur2, ...);

What is the order by clause?

The select statement does not provide the result set sorted. To sort the result set, we add the order by clause to the select statement. The order by clause is always evaluated after the from and select clause. Code: SELECT select_list FROM table_name ORDER BY column1 [ASC|DESC], column2 [ASC|DESC], …;

Say the types of SQL joins.

- inner join - left join - right join - full join

What is true about the SQL dialect? a) refers to the original SQL-86 version b) the way SQL coders communicate to each other c) the SQL implementation provided by each vendor d) the SQL property of being embedded in JAVA applications

CRUD refers to: a) create, read, update, drop b) create, read, update, delete c) create, retrieve, update, drop d) collect, read, update, delete e) none of the above

We want to select the employees that do not belong to any department: a) SELECT * FROM employee WHERE department_id is null; b) SELECT * FROM employee WHERE department_id is not null; c) SELECT * FROM employee WHERE department_id in (null); d) SELECT * FROM employee IF EXISTS department_id;

Assume table REGION has 3 rows with region_name = 'Americas', 2 rows with region_name = 'Europe', and 4 rows with other world regions. How many rows are updated by the following SQL code: UPDATE region SET region_name = 'America' WHERE region_name = 'Americas' AND region_name = 'Europe'; a) 0 b) 5 c) 3 d) 2 e) 4

About the following SQL code: DELETE FROM region LIMIT 2 WHERE region_name = 'Europe' a) deletes 2 rows corresponding to Europe region b) completes execution but deletes zero regions c) deletes the two first rows regardless of which region they refer to d) raises an error

If the following reasons are true, for which of them this SQL code raises an error: INSERT INTO department (`DEPARTMENT_ID`,`DEPARTMENT_NAME`,`LOCATION_ID`) VALUES (4, 'MARKETING2', 100, 1000); a) we are inserting a DEPARTMENT_ID that already exists and DEPARTMENT_ID is defined as the primary key b) the number of columns is different from the number of values c) we are inserting a value for LOCATION_ID (foreign key) that does not exist in table LOCATION where LOCATION_ID is defined as NOT NULL d) all of the previous reasons

We want to get the unique first names of the employees that are from Portugal: a) SELECT UNIQUE first_name FROM employees WHERE country = 'Portugal'; b) SELECT DISTINCT first_name FROM employees WHERE country = 'Portugal'; c) SELECT first_name FROM employees WHERE country = 'Portugal'; d) none of the above

We want to find the products whose prices are between 200 and 400: a) SELECT productName FROM products WHERE price > 200 AND < 400; b) SELECT productName FROM products WHERE price BIGGER 200 AND SMALLER 400; c) SELECT productName FROM products WHERE price BETWEEN 200 AND 400; d) SELECT productName FROM products WHERE price IN 200 AND 400;

About the left join, being table A the left table and B the right: a) return all of the records in table A that do not match any records in table B b) return only the records in table A that match the records in table B c) return all of the records in table A regardless if the records have a match in table B d) return all of the records in table B regardless if the records have a match in table A

Assuming that emp_no is the primary key, and that the job_title is unique. What does the following query does? SELECT e.first_name, t.title, e.emp_no FROM employees AS e, titles AS t WHERE e.emp_no = t.emp_no AND t.title = 'Senior Engineer'; a) retrieve the first name, title, and employee identifier of the senior engineers b) retrieve the first name, title, and employee identifier of the employees that are not senior engineers c) retrieve the first name, title, and employee identifier of employees where the employee identifier is equal to the title identifier d) none of the above

What retrieves the following query? SELECT T1.orderNumber, T1.status, SUM(T2.quantityOrdered * T2.priceEach) total FROM orders AS T1 INNER JOIN orderdetails AS T2 ON T1.orderNumber = T2.orderNumber GROUP BY T1.orderNumber; a) orderNumber, status, quantityOrdered, and priceEach b) orderNumber, status, and the order average c) orderNumber, status, and the total of the order d) orderNumber, status, the total of each order detail

What is true about the following query? SELECT o.orderNumber, p.productName, p.msrp, o.priceEach FROM products p JOIN orderdetails o ON p.productcode = o.productcode AND p.msrp > o.priceEach WHERE p.product code = 'S10_1678'; a) the query only retrieves data from table products b) the query uses an inner join c) the query uses a right join d) the query joins tables based on the MSRP in the MSRP as key column

What is true for this code: SELECT concat(p.product_code, ' ', p.product_name) FROM product AS p; a) retrieves two columns and presents as two separate columns in the result set: product code and product name b) retrieves two columns (product code and product name) and presents as one single column in the result set c) may retrieve more than two columns if product code is not unique d) all the options are wrong

We want to retrieve the average salary by year: a) SELECT YEAR, AVG(s.salary) FROM employees AS e, salaries AS s WHERE e.emp_no = s.emp_no GROUP BY e.hire_date; b) SELECT e.hire_date, AVG(s.salary) FROM employees AS e, salaries AS s WHERE e.emp_no = s.emp_no GROUP BY e.hire_date; c) SELECT YEAR(e.hire_date) AS year, SUM(s.salary) FROM employees AS e, salaries AS s WHERE e.emp_no = s.emp_no GROUP BY YEAR(e.hire_date); d) SELECT YEAR(e.hire_date) AS year, AVG(s.salary) FROM employees AS e, salaries AS s WHERE e.emp_no = s.emp_no GROUP BY YEAR(e.hire_date);

What is true about a view? a) it is a virtual table without physical rows b) the name of the view cannot be the same as the table c) the view does not physically store the data d) all the previous are true e) none of the above

The trigger can be activated in the following events: a) INSERT, UPDATE, DELETE b) SELECT, INSERT, UPDATE, DELETE c) INSERT, UPDATE d) INSERT, DELETE

What is true about triggers: a) triggers do not affect performance b) they may cause deadlock situations c) not appropriate for automatic auditing d) triggers are easy to debug

The following table with purchase orders is created: CREATE TABLE PURCHASE_ORDER (PONR CHAR(7) NOT NULL PRIMARY KEY, PODATE DATE, SUPNR CHAR(4) NOT NULL, FOREIGN KEY (SUPNR) REFERENCES SUPPLIER(SUPNR) ON DELETE CASCADE ON UPDATE CASCADE); What happens upon the deletion of a supplier? a) all purchase orders records tied to that supplier are also deleted b) the SUPNR of this supplier is replaced by a NULL value in PURCHASE_ORDER c) the SUPNR of this supplier is deleted in PURCHASE_ORDER d) the SUPNR of this supplier is only deleted in SUPPLIER

What is a inner join and how to code it?

An inner join returns all of the records in the left table (table A) that have a matching record in the right table (table B). Code: SELECT column_list FROM table_A A INNER JOIN table_B B ON A.key = B.key;

What is a left join and how to code it?

A left join returns all of the records in the left table (table A) regardless if any of those records have a match in the right table (table B). It will also return any matching records from the right table. Code: SELECT column_list FROM table_A A LEFT JOIN table_B B ON A.key = B.key;

What is a right join and how to code it?

A right join returns all of the records in the right table (table B) regardless if any of those records have a match in the left table (table A). It will also return any matching records from the right table. Code: SELECT column_list FROM table_A A RIGHT JOIN table_B B ON A.key = B.key;

How to do a full outer join with union, and what is it?

MySQL does not support full outer join, we need to emulate it. It returns all of the records from both tables, joining records from the left table (table A) that match records from the right table (table B). Code: SELECT * FROM table_1 LEFT JOIN table_2 ON table_1.id = table_2.id UNION ALL SELECT * FROM table_1 RIGHT JOIN table_2 ON table_1.id = table_2.id;

What is a left excluding join and how to code it?

The left excluding join return all of the records in the left table (table A) that do not match any records in the right table (table B). The where statement makes the difference with the left join (not excluding). Code: SELECT column_list FROM table_A A LEFT JOIN table_B B ON A.key = B.key WHERE B.key IS NULL;

What is a right excluding join and how to code it?

The left excluding join return all of the records in the right table (table B) that do not match any records in the left table (table A). The where statement makes the difference with the right join (not excluding). Code: SELECT column_list FROM table_A A RIGHT JOIN table_B B ON A.key = B.key WHERE A.key IS NULL;

Can we do a self join?

Yes, a self join: is used to query hierarchical data. For instance, the table employees contains the employees and their supervisors (who are also employees). To perform a self-join we must use table aliases.

What are aggregate functions?

Aggregate functions perform calculations on multiple values and return a single value. Aggregate functions are often used with the group by clause to calculate an aggregate value for each group.

Give examples of aggregate functions:

AVG, MAX, MIN, STDEV, COUNT, SUM

Give examples of math functions:

MOD, ROUND, TRUNCATE, SIN, SQRT, RAND, LOG, POW

Give examples of date functions:

CURDATE (CURRENT_DATE), DATEDIFF, DAY, DAYOFWEEK, NOW, MONTH, TIMEDIFF, YEAR

Give examples of string functions:

CONCAT, LENGHT, LOWER, REPLACE, SUBSTRING, TRIM, UPPER, INSTR

What is the group by clause and how to use it?

This clause groups a set of rows into a set of summary rows by values of columns or expressions. The group by clause returns one row for each group. Usually, this clause is used with aggregate functions such as SUM, AVG, MAX, MIN, and COUNT. Code: SELECT c1, c2, …, cn, aggregate_function(ci) FROM table_name WHERE where_conditions GROUP BY c1, c2, …, cn;

What is the having clause and how to use it?

The having clause is used in the SELECT statement to specify filter conditions for a group of rows or aggregates. The having clause is often used with the group by clause to filter groups based on a specified condition. Notice that the having clause applies a filter condition to each group of rows, while the where clause applies the filter condition to each individual row. Code: SELECT select_list FROM table_name WHERE search_condition GROUP BY group_by_expression HAVING group_condition;

Is having used before or after where?

After

What is a view?

A view is defined by means of an SQL query and its content is generated upon invocation of the view by an application or other query. We can run queries against the view.

Does the view have data?

A view does not physically store the data, so each time we query the view, the underlying query (view definition) is executed. The name of the view cannot be the same as the name of an existing table.

How to create a view?

CREATE [OR REPLACE] VIEW [db_name.]view_name [(column_list)] AS select-statement;

What is a trigger?

A trigger is a piece of SQL code consisting of declarative and(or procedural instructions and stored in the catalogue of the RDBMS. It is automatically activated and run by the RDBMS whenever a specified event (insert, update, delete) occurs and a specific condition is evaluated as true.

What are the two types of triggers?

- Row-level trigger: is activated for each row that is inserted, updated, or deleted (only ones supported by MySQL). - Statement-level trigger: is executed once for each transaction regardless of how many rows are inserted, updated, or deleted (not supported by MySQL).

How to create a trigger?

CREATE TRIGGER trigger_name trigger_time trigger_event ON table_name FOR EACH ROW BEGIN Code of what the trigger will do END; - trigger_time can be BEFORE or AFTER. - trigger_event can be INSERT, UPDATE, or DELETE.

What are the $$ in the following code? DELIMITER $$ CREATE TRIGGER employee_bi_update_comission BEFORE INSERT ON employee FOR EACH ROW BEGIN IF new.department_id = 80 THEN SET new.comission_pct = 0.15 END IF; END $$

To change the delimiter as in the body of what the trigger will do we do queries.

What are the advantages of triggers?

- Automatic monitoring and verification in case of specific events or situations. - Modelling extra semantics and/or integrity rules without changing the user front-end or application code. - Assign default values to attribute types for new tuples. - Synchronic updates in case of data replication. - Automatic auditing and logging, which may be hard to accomplish in any other application layer. - Automatic exporting of data.

SQL stands for: a) storing query language b) standard query language c) structured quality language d) none of the above

Assume the table EMPLOYEE has 20 rows, some employees have the same last name, there are only 4 unique last names. How many rows returns the following query: "SELECT DISTINCT last_name FROM employee LIMIT 10" a) 4 b) 20 c) 0 d) 10

Which type of join is defined with this statement: "Returns all of the records in the left table (table A) that have a matching record in the right table (table B)." a) RIGHT JOIN b) OUTER JOIN c) LEFT JOIN d) INNER JOIN

Assume that table SALARY has 4 columns, and table EMPLOYEES has 6 columns. All the columns have different column names. How many columns will be displayed using the following query: "SELECT * FROM salary AS a JOIN employees AS b ON a.emp_id = b.emp_no LIMIT 20" a) 0 b) 5 c) 10 d) 20

You need to display the salaries of employees sorted, which clause should you use? a) GROUB BY b) HAVING c) UPPER d) None of the above

You create a foreign key between a parent table and child table. You want to prevent rows from the parent table get deleted. What clause should you use? a) ON UPDATE CASCADE b) ON DELETE RESTRICT c) ON UPDATE RESTRICT d) ON DELETE CASCADE

What kind of join is the following query: SELECT a.customer_id, a.last_name, b.first_name, b.last_name FROM customer AS a INNER JOIN customer AS b ON a.last_name = b.first_name; a) LEFT EXCLUDING JOIN b) INNER EXCLUDING JOIN c) SELF-JOIN d) None of the above

What is true about the following ERD: a) to retrieve the name of cinema and the seat row you need to join cinema, theatre and seat. b) contains identifying and non-identifying relationships c) booking is a linking table between seat and theatre_film d) all of the above

You want to get the total amount of the salaries paid to the employees. What is the correct query? a) SELECT SUM(salary) FROM employees; b) SELECT salary FROM employees; c) SELECT SUM(salary) FROM employees GROUP BY employee_id; d) SELECT MAX(salary) FROM employees;

What is NOT true about a VIEW? a) a view does not physically store the data b) each time you query the view, the underlying query is executed c) a view can have the same name of a table d) a view is a virtual table without physical rows