SQL

What are the types of JOINs in SQL?

(INNER) JOIN - Retrieves records that have matching values in both tables involved. LEFT (OUTER) JOIN - Retrieves all records from the left and the match records from the right table RIGHT (OUTER) JOIN - retrieves all records from the right table and matched records from the left table FULL (OUTER) JOIN - retrieves all the records in both tables.

Write an SQL statement to perform SELF JOIN for Table_x with alias Table_1 and Table_2 on columns Col_1 and Col_2 respectively

(SELECT * FROM Table_X AS Table_1, Table_X AS Table_2 WHERE Table_1.Col_1 = Table_2.Col_2;)

Write an SQL statement to CROSS JOIN table_1 with table_2 and fetch col_1 from table_1 and col_2 from table_2, do not use aliases

(SELECT table_1.col_1, table_2.col_2 FROM table_1 CROSS JOIN as table_2;)

What are SQL Views

A "virtual" table that is propagated by one or more real time tables using SQL statements and functions. It is useful for: -concise presentation/simpole -security -aliases, name simplification

What is a Foreign Key?

A FOREIGN KEY comprises of single or collection of fields in a table that essentially refer to the PRIMARY KEY in another table. The table with the foreign key constraint is labelled as the child table, and the table containing the candidate key is labelled as the referenced or parent table.

UNION vs MINUS vs INTERSECT

UNION - combines and returns the result-set retrieved by two or more select statements MINUS - removes duplicates from the result-set obtained by the second SELECT query from the result-set obtained by the first SELECT query INTERSECT - combines the result-set fetched by the two SELECT statements where records from one match the other and then returns this intersection of result-sets.

What is a PRIMARY KEY

A constraint that uniquely identifies each row in a table - has an implicit NOT NULL constraint.

What is a Subquery

A query within another query

What are Tables, Fields, and Records

Tables - an organized collection of data in the form of rows and columns Fields - columns in a table Records - rows in a table

What is an Alias in SQL?

A temporary name assigned to the table or table column for the purpose of a particular SQL query. Can be used to secure the real names of database fields (security). AKA correlation name

Write a SQL statement to add primary key constraint 'pk_a' for table 'table_a' and fields 'col_b' and 'col_c'

ALTER TABLE table_a ADD CONSTRAINT pk_a PRIMARY KEY (col_b, col_c);

Write an SQL statement to add a FOREIGN KEY 'col_fk' in 'table_y' that references 'col_pk' in 'table_x'

ALTER TABLE table_y ADD FOREIGN KEY (col_fk) REFERENCES table_x (col_pk);)

Write an SQL statement to add PRIMARY KEY 't_id' to the table 'teachers'

ALTER TABLE teachers ADD PRIMARY KEY (t_id);

What type of integrity constraint does the foreign key ensure?

Referential Integrity

What are SQL Stored Procedures

SQL Stored Procs are a batch of SQL statements that can perform a special task and be executed repeatedly.

What is SQL

SQL stands for Structured Query Language. SQL lets you access and manipulate databases. SQL is a relational database

SQL vs MySQL

SQL us a standard language for retrieving and manipulating structured databases. MySQL is a relational database management system, like SQL Server

What is a join in SQL?

Used to combine records (rows) from two or more tables in a database based on a related column between the two

What is the SELECT Operator

Used to select data from a database. The data returned is stroed in a result table, called the result-set. Common clauses used in conjuncture: WHERE, ORDER BY (ASC or DESC)

What are constraints in SQL

Used to specify rules concerning data in the table. E.g. NOT NULL CHECK DEFAULT UNIQUE INDEX PRIMARY KEY FOREIGN KEY