SQL

What are the four basic data operations you can do using SQL?

1. Insert/add new data to a table. 2. Select/get data from a table or tables. 3. Update/change data in a table. 4. Delete/remove data in a table.

What is a foreign key?

A foreign key is a key used to link two tables together. This is sometimes called a referencing key. Foreign key is a column or a combination of columns whose values match a Primary Key in a different table. This is to establish a relationship between records in a separate table.

What are the names of the four 'basic' SQL data commands?

Insert - Select - Update - Delete

Can you have more than one primary key in one table?

No. A table can have only one primary key constraint.

How do you add a record to a table?

SQL INSERT INTO statement is used to add new rows of data to a table in the database. INSERT INTO TABLE_NAME (COLUMN1, COLUMN2) VALUES (VALUE1, VALUE2);

What is the difference between WHERE and HAVING?

WHERE Does not work with aggregates like SUM HAVING Can compare aggregates to other values

What is a many to many relationship?

Many-to-Many relationship is defined as a relationship between two tables where many rows from one table can have multiple matching rows in another table. Neither table can support a foreign key to relate the tables, so a junction table (join table or associative entity) is created. A junction table is a database table that contains foreign key references to two or more other database tables. It is the standard way of creating a many-to-many relationship between tables

What is a cross join?

A cross join that does not have a WHERE clause produces the Cartesian product of the tables involved in the join. The size of a Cartesian product result set is the number of rows in the first table multiplied by the number of rows in the second table. SELECT p.BusinessEntityID, t.Name AS Territory FROM Sales.SalesPerson p CROSS JOIN Sales.SalesTerritory t ORDER BY p.BusinessEntityID;

What is the difference between inner join and outer join?

A join is used to compare and combine and return specific rows of data from two or more tables in a database. Inner Join An inner join focuses on the commonality between two tables. When using an inner join, there must be at least some matching data between two (or more) tables that are being compared. SELECT COLUMN1 FROM TABLE_NAME INNER JOIN TABLE_NAME ON TABLE_NAME1.COLUMN1 = TABLE_NAME2.COLUMN1 Outer Join An outer join returns a set of records (or rows) that include what an inner join would return but also includes other rows for which no corresponding match is found in the other table Left Outer Join (Left Join) Right Outer Join (Right Join) Fill Outer Join (Full Join)

What is a primary key?

A primary key is a field in a table which uniquely identifies each row/record in a database table. Primary keys must contain unique values and cannot have NULL values.A table can have only one primary key, which may consist of single or multiple fields. When multiple fields are used as a primary key, they are called a composite key

What is a nested query, or a subquery?

A subquery is a query that is nested inside a SELECT, INSERT, UPDATE, or DELETE statement, or inside another subquery. A subquery can be used anywhere an expression is allowed.

List each of the ACID properties that collectively guarantee that database transactions are processed reliably.

ACID Atomicity Consistency Isolation Durability

What is the difference between DELETE, TRUNCATE, and DROP?

DELETE 1. Removes some or all rows from a table 2. A WHERE clause can be used to remove some rows. If no Where condition is specified, all rows will be removed 3. Causes all DELETE triggers on the table to fire 4. Removes rows row-by-row one at a time and records an entry in the Transaction logs, thus is slower than TRUNCATE 5. It is a DML command as it is just used to manipulate/modify the table data. It does not change any property of a table. TRUNCATE 1. Removes all rows from a table 2. No triggers are fired on this operation because it does not operate on individual rows 3. TRUNCATE is not possible when a table is reference by a foreign key. 4. It is a DDL command as it resets IDENTITY columns DROP 1. Removes table from database 2. No DML triggers will be fired 3. Can't be rolled back 4. DDL command

How do you drop a table?

DROP TABLE statement is used to remove a table definition and all data, indexes, triggers, constraints, and permission specifications for that table. DROP TABLE TABLE_NAME

What are various DDL commands in SQL? Give brief description of their purposes.

Data Definition Language: Defines data structure CREATE - it creates a new table, a view of a table, or other object in database. ALTER - it modifies an existing database object, such as a table DROP - it deletes an entire table, a view of a table or other object in the database.

What are various DML commands in SQL? Give brief description of their purposes.

Data Manipulation Language: Manipulate data SELECT - it retrieves certain records from one or more tables. INSERT - it creates a table. UPDATE - it modifies records. DELETE - it deletes records.

What is an ERD?

Entity Relational Diagram It is a snapshot of data structures that show entities (tables) in a database and relationships between tables within that database. Elements 1. Entities, things for which we want to store information 2. Attributes, data that we want to collect for an entity 3. Relationship, descriptions of the relationship between entities

What is the difference between INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL JOIN?

INNER JOIN: Returns all rows when there is at least one match in BOTH tables LEFT JOIN: Return all rows from the left table, and the matched rows from the right table RIGHT JOIN: Return all rows from the right table, and the matched rows from the left table FULL JOIN: Return all rows when there is a match in ONE of the tables

What is the difference between IN and EXISTS?

In Returns true if a specified value matches any value in a subquery or a list. Works best for a small finite set of data. When used and combined with a subquery, the database must process the entire subquery first, then process the overall query as a whole. SELECT COLUMN1 FROM TABLE_NAME WHERE COLUMN1 IN (VALUE1, VALUE2); Exists Return true if a subquery contains any rows. Is better for subqueries SELECT COLUMN1 FROM TABLE_NAME WHERE COLUMN1 EXISTS (SELECT COLUMN1 FROM TABLE_NAME WHERE CONDITION);

What are the various constraints in SQL?

NOT NULL Indicates that a column cannot store NULL value UNIQUE Ensures that each row for a column must have a unique value PRIMARY KEY A combination of a NOT NULL and UNIQUE. Ensures that a column or columns have a unique identity which helps to find a particular record in a table more easily and quickly FOREIGN KEY Ensure the referential integrity of the data in one table to match values in another table CHECK Ensures that the value in a column meets a specific condition DEFAULT Specifies a default value for a column

What is normalization?

Normalization is the process of organizing data in a database. This includes creating tables and establishing relationships between those tables according to rules designed both to protect the data and to make the database more flexible by eliminating redundancy and inconsistent dependency. There are 6 normal forms

What is the difference between Order By and Group By?

ORDER BY is used for changing the order in which records are returned according to specified column. GROUP BY is used in conjunction with the aggregate functions to group the result set by one or more columns.

What is a one to many relationship?

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. This relationship can be created using primary key-foreign key relationship.

What is a one to one relationship?

One-to-One relationship is defined as the relationship between two tables where both tables should be associated with each other based on only one matching row. This relationship can be created using Primary key-unique foreign key constraints

What is an orphan?

Orphaned records are records that reference a key which no longer exists in the foreign table. If referential integrity is enforced by using a foreign key constraint, this cannot happen.

What is referential integrity?

Referential integrity means that the foreign key in any referencing table must always refer to a valid row in the referenced table. Referential integrity ensures that the relationship between two tables remain synchronized during updates and deletes.

How do you add a column to the table?

The ALTER TABLE statement is used to add, delete, or modify columns in an existing table and can also be used to add and drop various constraints in an existing table. ALTER TABLE TABLE_NAME ADD/DROP COLUMN_NAME; ALTER TABLE TABLE_NAME ALTER/MODIFY COLUMN_NAME DATATYPE;

How can you retrieve rows from a database table?

SQL SELECT statement is used to fetch the data from a database table which returns data in the form of result table. These tables are called result-sets. SELECT COLUMN1, COLUMN2 FROM TABLE_NAME

What is an aggregate function?

SQL aggregate functions return a single value, calculated from values in a column. AVG() - Returns the average value COUNT() - Returns the number of rows FIRST() - Returns the first value LAST() - Returns the last value MAX() - Returns the largest value MIN() - Returns the smallest value SUM() - Returns the sum

What is the purpose of SELECT INTO?

The SELECT INTO statement creates a new table and populates it with the result set of the SELECT statement. SELECT INTO can be used to combine data from several tables or views into one table. It can also be used to create a new table that contains data selected from a linked server. SELECT c.FirstName, c.LastName, e.JobTitle, a.AddressLine1, a.City, sp.Name AS [State/Province], a.PostalCode INTO dbo.EmployeeAddresses FROM Person.Person AS c JOIN HumanResources.Employee AS e ON e.BusinessEntityID = c.BusinessEntityID JOIN Person.BusinessEntityAddress AS bea ON e.BusinessEntityID = bea.BusinessEntityID JOIN Person.Address AS a ON bea.AddressID = a.AddressID JOIN Person.StateProvince as sp ON sp.StateProvinceID = a.StateProvinceID;

What is the purpose of AND and OR?

The WHERE clause can be combined with AND, OR, and NOT operators to filter records based on more than one conditions The AND operator displays a record if all the conditions separated by AND is TRUE. The OR operator displays a record if any of the conditions separated by OR is TRUE. The NOT operator displays a record if the condition(s) is NOT TRUE.

What is the WHERE clause?

The WHERE clause is used to filter records and extract only those records that fulfill a specified condition. SELECT COLUMN1, COLUMN2 FROM TABLE_NAME WHERE CONDITION;

Can a primary key contain more than one column?

Yes. A primary key with more than one column is called a composite key.