Questions

1) What is SQL and what does it stand for ?

SQL stands for Structured Query Language and is a specific language used for accessing and manipulating databases.
This language is a universal standard that all database programs must adhere to and they must all support the same keywords.
Most of the SQL database programs also have their own proprietary extensions in addition to the SQL standard.
SQL statements are not case sensitive.


2) What is the ANSI Standard ?

This stands for American National Standards Institute.
Despite the existence of this standard most SQL code is not completely portable without adjustment.


3) What is the difference between a DBMS and a RDBMS ?

The biggest difference is that DBMS applications store data as files while RDBMS stores data in tables.
An RDBMS is a DBMS which is based on the relational model.


4) What are the main differences between Access and SQL Server ?

Both are RDBMS applications.
Access has a maximum size of 2GB.


5) What is SQL Server Express ?

SQL Server 2005 Express replaced MSDE and is a FREE scaled down version of SQL Server 2005.
It comes with SQL Server Management Studio Express.
The database has a maximum size of 4GB.
The sample databases (AdventureWorks and Northwind) are not installed by default but can be downloaded.


6) What is Normalisation ?

This is the term given to efficiently organising the tables in a database by removing duplicate data and ensuring the correct data dependencies.
By normalising your database you make it more efficient and help to reduce its size.
The database community has developed a series of guidelines for ensuring that databases are normalized.
These are referred to as normal forms and are numbered from one 1NF to six 6NF.


7) What is Denormalisation ?

This is the process of adding redundant data/columns in an attempt to optimise the performance.


8) What is Referential Integrity ?

This means that the foreign key in any referencing table must always refer to a valid row in the referenced table.
Referential integrity constraints can be applied to ensure that a relationship between two tables remains synchronized during updates and deletes.
When applied referential integrity can protect against accidental additions and deletions.
For example before you delete a Customer you must delete all the Orders first.


9) What is an Orphaned Record ?

This is a record in one table that does not have a corresponding entry in a related table.


10) What is an Entity Relationship Diagram ?

An entity relationship (ER) diagram is a graphical representation of entities and their relationships to each other (one-to-one, one-to-many, many-to-many).
It is commonly used to describe the organisation of data within a database.
These diagrams use square, circle and rhombus.


11) What is Replication ?

Replication is a way of keeping data synchronised in multiple databases and is normally monitored by a qualified DBA (DataBase Administrator).


12) What is a Transaction ?

A transaction is a group of queries (and/or statements) that must all be either completed or aborted.
Using transactions allows you to ensure data integrity in a database.
If any of the queries fail for whatever reason the transaction can be rolled back to return the database to its original state.


13) What is a Table ?

A table is a set of related records organised in horizontal rows with a specific number of columns. Tables often have meta-data associated with them, for example constraints.


14) What is a Domain Table ?

A domain table is a table whose sole purpose is to provide a list of acceptable values, like a lookup table.


15) What is a View ?

A view is a pre-compiled query which pulls data from one or more tables.
A view is a virtual table composed of the result set of a query.
A view does not store any actual data.
A view is not actually part of the database schema and is automatically updated when the corresponding table(s) are updated. Views can be read only or updatable.


16) What is the difference between a View and a Table ?

*) Views allow you to restrict access to particular subsets of data.
*) You can add/remove columns easily in a view without modifying the underlying schema.
*) Views can hide joins (ie complexity) and present the user with a more denormalised subset of the data.
*) Retrieving data from a view is faster than from a table.
*) Views cannot be used to override constraints or referential integrity defined on the tables.
*) Never use a view to access or call another view.


17) Can you Insert, Update and Delete from a View ?

Yes. However there are some restrictions.
Any modification statements must reference columns from only one table. The workaround is to use a INSTEAD OF trigger.
If a view contains joins between tables you can only insert and update one table in the view and you cannot delete any rows.
You cannot modify any data in views that are based on union queries.
You cannot modify any data in views that use GROUP BY or DISTINCT


18) What is a Query ?

A query is a request for some data from one or more tables using the SELECT statement.


19) What is a Sub Query ?

A sub query is a query that is nested inside another query.
These must be enclosed in parentheses.
It must be put in the right hand of the comparison operator.
It cannot contain an ORDER BY.

SELECT t_Orders.OrderID 
FROM (
    SELECT *
    FROM Orders
) AS t_Orders
WHERE t_Orders.EmployeeID > 5

SELECT Products.ProductID
FROM Products
WHERE SellingPrice > (
    SELECT Max(UnitPrice)
    FROM OrderDetails
    WHERE Discount = .25
)


20) Can you describe the different types of SubQueries in SQL Server ?

*) Single Row
*) Multiple Row
*) Multiple Column


21) What is a Parameterised Query ?

This is a query that accepts parameters.
This type of query will prompt you for the necessary arguments.
This type of query is useful when you perform the same type of query regularly but the values you want to search for change.
Example - Is it the same or different in Access ?


22) What are the standard Data Manipulation elements ?

These allow you to select, add, update and delete data.
SELECT - is used to retrieve zero or more rows from one or more tables.
INSERT - is used to add zero or more rows to an existing table.
UPDATE - is used to modify the values of a set of existing table rows.
DELETE - removes zero or more existing rows from a table.
MERGE - is used to combine the data from multiple tables.


23) What are the standard Data Definition elements ?

These allow you to define new tables and associated elements.
CREATE - causes an object (a table, for example) to be created within the database.
DROP - causes an existing object within the database to be deleted, usually irretrievably.
TRUNCATE - deletes all data from a table (non-standard, but common SQL command).
ALTER - command permits the user to modify an existing object in various ways -- for example, adding a column to an existing table.


24) What are the standard Data Control elements ?

These allow you to handle the authorization and permissions to control who has access to see or manipulate what data within the database.
GRANT - authorises one or more users to perform an operation or a set of operations on an object.
REVOKE - removes or restricts the capability of a user to perform an operation or a set of operations.


25) What is a Column Alias and how do you create one ?

A column Alias (or field alias) allows you to change the column heading that will be displayed.
A column alias is often used if a column name is cryptic, difficult to type, too long or too short.
You must include a column alias when you have an expression in your SELECT.
You should always include the AS keyword (although it is optional in SQL Server).
It is best practice to not include spaces in your column alias names.

SELECT Employees.FirstName AS c_ColumnAlias 
SELECT Employees.FirstName AS "Column Alias with Space"
SELECT Employees.FirstName AS [Column Alias with Space]
FROM Employees


26) Where can you use a Column Alias ?

You can only use a column alias in an ORDER BY.
SQL queries are executed in the following order:
FROM > WHERE > GROUP BY > HAVING > SELECT > ORDER BY


27) What is a Table Alias and how do you create one ?

A table alias lets you change the table name referenced in your SQL or lets you reference the same table more than once in a self join.
If you define a table alias then you must use the alias in your query.
It is best practice to always include the AS keyword (although it is optional in both Access and SQL Server).

SELECT t_Cust.CustomerName, 
       t_Cust.Address +', '+ t_Cust.City +', '+ t_Cust.PostalCode AS c_FullAddress
FROM Customers AS t_Cust


28) Where can you use a Table Alias ?

You can use a table alias everywhere.
SQL queries are executed in the following order:
FROM > WHERE > GROUP BY > HAVING > SELECT > ORDER BY


29) Is there any impact on performance when you use table or column aliases ?

??


30) What is the difference between Single Quotes and Double Quotes ?

Single quotes are used for string literals and date literals.
Double quotes are used for objects or identifiers.
Double quotes can also be used for aliases.


31) How do you concatenate columns ?

SELECT Employees.FirstName + ' - ' + Employees.LastName 
FROM Employees

In Access you can also use ampersand (&).


32) Can you use any Date Literal Constants ?

WHERE Employees.HireDate > #01/01/1994#       'Access only - check  


33) What is a Null Value ?

All relational databases support a special value called a Null Value.
This represents an unknown or missing value.
This is very different from the number zero or a zero-length string.
A null value means that there is no value and therefore it cannot be compared to anything, not even another null value.
Null values are ignored when you use any of the Aggregate Functions.


34) How can you check for Null Values ?

You can check for null values by comparing the value to the NULL keyword or by using the built-in functions.
ISNULL function - Access
ISNULL function - SQL Server

SELECT * 
       IIF(ISNULL(ColumnName),0,ColumnName) 'Access only
       ISNULL(ColumnName,0,ColumnName) 'SQL Server only
FROM Employees
WHERE Region IS Null
WHERE NOT Region IS NULL
WHERE ISNULL(Region) = False


35) Are the Data Types the same in Access and SQL Server ?

No. They are very different.

ANSISQL ServerAccess
??????


36) Can you give some examples of the different SQL Server numeric data types ?

BIGINT - 
BIT -
DECIMAL (DEC) -
DOUBLE PRECISION -
FLOAT - approximate value
INTEGER (INT) -
NUMERIC -
REAL - approximate value
SMALLINT -
TINYINT -


37) Can you give some examples of the different SQL Server string / character data types ?

CHARACTER (CHAR) - 
CHARACTER VARYING (VARCHAR) -
CHARACTER LARGE OBJECT (CLOB) -
NATIONAL CHARACTER -
NATIONAL CHARACTER VARYING (NVARCHAR) -


38) Can you give some examples of the different SQL Server binary data types ?

BINARY - 
BINARY VARYING (VARBINARY) -
BINARY LARGE OBJECT (BLOB) -


39) Can you give some examples of the different SQL Server date / time data types ?

DATE - 
TIME -
TIMESTAMP -
INTERVAL -


40) What is the difference between REAL and FLOAT in SQL Server ?

The minimum and maximum values that can be stored are different.
REAL - min -3.4E38, max 3.4E38
FLOAT - min -1.79E308, max 1.79E308


41) What is the difference between VARCHAR and VARCHAR(max) in SQL Server ?



42) What is the difference between VARCHAR and NVARCHAR in SQL Server ?



43) What is the difference between CHAR and VARCHAR in SQL Server ?



44) What new Data Types were introduced in SQL Server 2008 ?



45) Is there a Boolean data type ?

SQL Server does not have a Boolean data type. You need to use BIT which accepts 0, 1 and Null.
Access ?


46) What is an Operator ?

These are reserved words used primarily in the WHERE statement to help perform conditions.
These operators can be divided into 3 categories
*) Arithmetic: +, -, *, /, %
*) Comparison: =, !=, <>, >, <, >=, <=, !<, !>
*) Logical - these return True or False


47) Can you give some examples of Comparison Operators ?

SELECT Employees.FirstName + ' ' + Employees.LastName 
FROM Employees
WHERE Employees.HireDate > DATESERIAL(1994, 1, 1)
WHERE Employees.HireDate > #01/01/1994# 'Access only


48) Can you give some examples of Logical Operators ?

ALL - compares one value to a column of values produced by a subquery. If all the column values are true then it returns true
AND -
ANY - (SQL Server only) compares one value to a column of values produced by a subquery. If any of the column values are true then it returns true
BETWEEN -
EXISTS -
IN -
IS NULL -
LIKE -
NOT -
OR -
SOME - (SQL Server only) alternative to ANY
UNIQUE - this searches every row for uniqueness (no duplicates)
XOR - (Access only)
EQV - (Access only)


49) Can you describe the ALL operator ?

This can be used to select all the records of a Select statement.
It compares a value to every value in a list or sub query result.
This operator must be preceded by a comparison operator and evaluates to True if the query returns no rows.

WHERE expression operator ALL subquery 


50) Can you describe the AND operator ?

This can be used to display a row if both the conditions are True.

SELECT * 
FROM Customers
WHERE Customers.Country = 'UK'
AND Customers.City = 'Cowes'


51) Can you describe the ANY operator ?

Not available in Access. Only SQL Server.
This is exactly the same as SOME.


52) Can you describe the BETWEEN operator ?

This statement is also known as a Range Query.
This is a query that returns everything between two values (inclusive).
The BETWEEN operator is used to select values within a range.

SELECT Orders.OrderID 
FROM Orders
WHERE Orders.OrderID BETWEEN 10254 AND 10258


53) Can you describe the EXISTS function ?

This checks the existence of a result of a subquery
It tests whether a subquery fetches at least one row.


54) Can you describe the IN operator ?

This compares values to see if they are in a list.

SELECT Employees.EmployeeID 
FROM Employees
WHERE Employees.FirstName IN ('Andrew','Anne')


55) Can you describe the IS NULL operator ?

??

SELECT Orders.OrderID 
FROM Orders
WHERE Orders.ShipPostalCode IS NULL


56) Can you describe the LIKE operators in ANSI-92 ?

This compares a column value with a specific pattern.
The data type of the column can be date or character/string.
_ (underscore). A single character.
% (percent). Any zero or more characters.
[ ] (brackets). Any single characters inside the bracket.
[ ^ ] (caret in brackets). Any single characters not inside the bracket.
[ - ] (dash in brackets) - Any values in a range in ascending order.
[ ! - ] (exclamation and dash) - Any values not in a range.

SELECT Orders.ShipCity 
FROM Orders
WHERE Orders.ShipCity LIKE 'Seattle'
WHERE Orders.ShipCity LIKE 'Se%'
WHERE Orders.ShipCity LIKE 'B_ll'
WHERE Orders.ShipCity LIKE 'B[ae]ll'
WHERE Orders.ShipCity LIKE 'B[^ae]ll'
WHERE Orders.ShipCity LIKE '[!ab]%'


57) Can you describe the LIKE operators in ANSI-89 ?

??
? (question mark). A single character.
* (asterisk). Any zero or more characters.
# (hash). Any single numeric digit.


58) Which ANSI standard is used by the following methods:

SQL Server - ANSI-92
Access 2007 Find & Replace - ANSI-89 (default)
ADO - ANSI-92
OLE DB - ANSI-92
ACE -
DAO Jet - ANSI-89
ODBC Drivers -


59) Can you describe the NOT operator ?

This can be used to return the opposite of a True or False condition.

SELECT * 
FROM Customers
WHERE NOT Customers.Country = 'USA'


60) Can you describe the OR operator ?

This can be used to only display a row if either of the conditions are True.

SEELCT * 
FROM Customers
WHERE Customers.Country = 'UK'
OR Customers.Country = 'Italy'


61) What is the SELECT statement ?

This command contains important clauses and the order of these clauses is very important.
FROM - is used to indicate from which tables (or views) the data is to be taken.
WHERE - is used to apply a condition to the SELECT.
GROUP BY - is used to combine rows with related values into elements of a smaller group of rows. This column often appears in the list of displayed columns.
HAVING - is used to apply a condition to the GROUP BY.
ORDER BY - is used to identify which columns are used to sort the resulting data.


62) How do you select everything from a table ?

SELECT * FROM TableName 
SELECT ALL * FROM TableName
SELECT TableName.* FROM TableName


63) What is the difference between DISTINCT and DISTINCTROW ?

DISTINCT - checks for unique values only in the fields selected for output and eliminates duplicate rows
DISTINCTROW - (Access only) checks for unique values in all fields in the table you are querying, not just the fields selected for output.

SELECT DISTINCT Employees.FirstName 
SELECT DISTINCTROW Employees.FirstName
FROM Employees


64) How can you return the top 5 rows from a query ?

SELECT TOP 5 
       Customers.CompanyName
FROM Customers


65) How can you return the top 10% of the rows from a query ?

SELECT TOP 10 PERCENT 
       Customers.CompanyName
FROM Customers


66) Can you describe the ORDER BY clause ?

??

ORDER BY Customers.CompanyName ASC      'default  
ORDER BY Customers.CompanyName DESC
ORDER BY Customers.CompanyName, Customers.ContactName


67) Write a query that gets employees that have a salary greater than 5000 without using the WHERE clause ?

HAVING with no GROUP BY



68) Describe the different types of JOINS ?

INNER JOINS - These include Self Joins.
OUTER JOINS - These include Left, Right and Full.
CROSS JOIN - Returns the cartesian product.


69) What is an INNER JOIN ?

Also known as an Equi Join or Simple Join.
An inner join connects two tables with a common column name.
This is the most common type of join and only returns the rows that meet a specific criteria.
This is often the relationship between a primary key and a foreign key (but it doesn't have to be).
Any rows that do not satisfy the join condition are discarded.


Display all the columns in the Order table for a specific company name.

SELECT Orders.* 
FROM Orders
INNER JOIN Customers
ON Customers.CustomerID = Orders.CustomerID
WHERE Customers.CompanyName = 'Alfreds Futterkiste'

The INNER keyword is optional in SQL Server but not in Access.


70) What is a SELF JOIN ?

This is a type of INNER JOIN where a table is joined to itself.
This means that the table must appear twice and therefore a table alias name must be used.


Display all employees that have a supervisor assigned to them.

SELECT Employees.LastName, 
       Employees.FirstName,
       t_Supervisors.LastName,
       t_Supervisors.FirstName
FROM Employees
INNER JOIN Employees AS t_Supervisors
ON Employees.EmployeeID = t_Supervisors.ReportsTo


71) What is a Natural Join ?

This is an inner join but without specifying the join columns.
The columns are automatically matched based on identical column names.
This is not supported in SQL Server or Access.


72) What is the difference between an INNER JOIN and an OUTER JOIN ?

An inner join only returns rows that meet the criteria.
An outer join returns rows that meet the criteria plus all the rows from one or both sides of the join.


73) What is an OUTER JOIN ?

There may be times when you want to return rows from one table even if there are no matching rows.
This may leave some of the field entries blank, or "Null."
There are three types:
LEFT OUTER JOIN - (or left join) - returns rows that meet the criteria plus all rows from the left table.
RIGHT OUTER JOIN - (or right join) - returns rows that meet the criteria plus all rows from the right table.
FULL OUTER JOIN - (or outer join) - returns rows that meet the criteria plus all the rows from both tables.


74) What is a LEFT OUTER JOIN ?

Also referred to as a Left Join.
A left outer join is where all the rows in the "one" table (in the "one-to-many" relationship) are returned and only matching rows from the "many" table.


Display all Customer Names irrespective of whether they have any orders. Everything from the Customers table.

SELECT Employees.Country, 
       Orders.OrderID
FROM Orders
LEFT OUTER JOIN Employees
ON Employees.Country = Orders.ShipCountry

The OUTER keyword is optional in SQL Server but not in Access.


75) What is a RIGHT OUTER JOIN ?

Also referred to as a Right Join.
A right outer join is where all the rows in the "many" table (in the "one-to-many" relationship) are returned and only matching rows from the "one" table.
If the Orders tables contained any Orphans (i.e. an order without a customer) this query would find it.
If you deleted the first Customer record in the Customers table (Alfreds Futterkiste) this would display the orphans.


Display a list of all orders that do not have a customer associated with them. Everything from the Orders table.

SELECT Employees.Country, 
       Orders.OrderID
FROM Orders
RIGHT OUTER JOIN Employees
ON Employees.Country = Orders.ShipCountry

The OUTER keyword is optional in SQL Server but not in Access.


76) What is a FULL OUTER JOIN ? (not supported in Access)

An outer join will show records from both tables, and displays nulls for missing matches on either side.
This returns all the orphans in both tables.
This is not used that often.

SELECT Customers.CompanyName, 
       Orders.OrderID
FROM Customers
FULL OUTER JOIN Orders
ON Orders.CustomerID = Customers.CustomerID


77) What is a CROSS JOIN ? (not supported in Access)

A cross join returns the Cartesian product of all the rows in all the tables.
The size of the result is the number of rows in one table multiplied by the number of rows in the other table.
This is the result of joining two tables with no join condition. Each row in one table is matched with each row in the other table.
These joins are almost never used, except to generate all possible combinations of records from tables that do not share a common element.

SELECT * 
FROM Customers
CROSS JOIN Orders

If a WHERE statement is added to a cross join then this creates an inner join ?


78) What are Aggregate Functions ?

Also known as Group Functions or Total Functions.
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 total


79) Can you explain how the GROUP BY and ORDER BY statements can be used in conjunction with the Aggregate Functions ?

GROUP BY - controls the way the data is summarised.
ORDER BY - sorts the summarised data.


80) GROUP BY - Display a list of all the different job titles and the number of employees with each title.

SELECT Employees.Title, 
       COUNT(Employees.Title)
FROM Employees
GROUP BY Employees.Title


81) GROUP BY - Display a list of customers and the corresponding number of orders they have placed.

SELECT Customers.CompanyName, 
       COUNT(Orders.OrderID)
FROM Customers,
INNER JOIN Orders
ON Orders.CustomerID = Customers.CustomerID
GROUP BY Customers.CompanyName


82) GROUP BY - Display a list of cities showing how many customers in each city.

SELECT Customers.City, 
       COUNT(*)
FROM Customers
GROUP BY Customers.City


83) GROUP BY - Display a list of all the customers showing the total number of orders for each customer.

SELECT Customers.CompanyName, 
       COUNT(*) As c_Total_Orders
FROM Orders
INNER JOIN Customers
ON Customers.CustomerID = Orders.CustomerID
GROUP BY Customers.CompanyName


84) GROUP BY - Display a list of customers and their corresponding number of orders that have a company name that starts with the letter F.

SELECT Customers.CompanyName, 
       COUNT(Orders.CustomerID)
FROM Customers,
INNER JOIN Orders
ON Orders.CustomerID = Customers.CustomerID
WHERE LEFT(Customers.CompanyName,1) = 'F'
GROUP BY Customers.CompanyName


85) HAVING - Display a distinct list of cities that have more than one customer/company.

SELECT DISTINCT Customers.City 
FROM Customers
GROUP BY Customers.City
HAVING COUNT(Customers.City > 1


86) HAVING - Display a list of all the job titles and the number of people that have a title where there is more than one person with that title.

SELECT Employees.Title, 
       COUNT(Employees.Title)
FROM Employees
GROUP BY Employees.Title
HAVING COUNT(Employees.Title) > 1


87) ORDER BY - Display a list of all the countries that orders have been shipped to and the number of orders and the total value of the freight shipped.

SELECT Orders.ShipCountry, 
       COUNT(Orders.OrderID),
       SUM(Orders.Freight)
FROM Orders
GROUP BY Orders.ShipCountry
ORDER BY SUM(Orders.Freight)


88) What are Scalar Functions ?

Scalar functions return a single value.
UCASE - returns the value converted to upper case.
LCASE - returns the value converted to lower case.
LEN - returns the length of a value.
ROUND - returns the value rounded to a specific number of decimals.
FORMAT - returns the value in a specific format.
LTRIM - returns the text string without leading spaces.
RTRIM - returns the text string without trailing spaces.
TRIM - returns the text string without leading and trailing spaces.


89) How do you display the current date and time ?

SELECT NOW       'Access only  
SELECT GETDATE 'SQL Server only


90) How do you replace characters ?

There is no REPLACE function when you use OLEDB

SELECT REPLACE   'Access sometimes  
SELECT REPLACE 'SQL Server


91) How do you display the first 3 characters ?

SELECT LEFT(ColumnName,3) 
SELECT MID(ColumnName,1,3) 'Access only
SELECT SUBSTRING(ColumnName,1,3) 'SQL Server only


92) How do you display the position of a character ?

SELECT INSTR(ColumnName,'a')         'Access only 
SELECT CHARINDEX(ColumnName,'a') 'SQL Server only


93) What is a Constraint ?

A constraint defines a condition (or rule) that must be adhered to during the process of adding, modifying or deleting data from a database.
These can be column based, table based, key based or referential integrity based and can be used to prevent data dependent deletion and help enforce business rules.
There are two ways to create constraints: CREATE TABLE and ALTER TABLE.


94) Can you give some examples of constraints ?

PRIMARY KEY -
FOREIGN KEY -
NOT NULL - indicates that a column cannot store the Null value
UNIQUE - ensures that each row contains a unique value
CHECK - ensures that the value meets a specific condition
DEFAULT - specifies a default value for a column


95) What is the difference between PRIMARY KEY and UNIQUE ?

There can only be one column in a table that has a Primary Key constraint.
A column with a Unique constraint can contain a Null Value, unless explicitly declared otherwise.


96) Can you describe the 'Primary Key' constraint ?

The primary key is an attribute that is assigned to a column to ensure it contains unique values.
The primary key serves to uniquely identify each row in the table.
A table can only have one primary key; however, it can include more than one attribute (called a composite or concatenated primary key).
It will create a clustered index automatically.

CREATE TABLE MyTable 
(
    MyColumn int PRIMARY KEY
)


97) Can you describe the 'Foreign Key' constraint ?

A foreign key is an attribute that is assigned to a column to indicate a link between this column and a primary key or unique column of another table.
A table can contain one or more foreign keys.

CREATE TABLE MyTable 
(
    MyColumn int FOREIGN KEY REFERENCES MyTable2(MyColumn2)
)


98) What are the Foreign Key Cascade Events ?

Also known as Cascading Referential Integrity Constraints.
When a referential integrity rule is broken additional actions can be taken to preserve the data integrity.
Foreign keys can be defined with cascade rules:
*) Cascade Delete - when a row is deleted any rows from other tables that have a foreign key matching the primary key should also be deleted.
*) Cascade Update - when a primary key is updated any rows from other tables that have a foreign key matching the primary key are also updated.
*) Set to Null - when a primary key is updated any rows from other tables that have a foreign key matching the primary key are set to null.

REFERENCES MyTable2(MyColumn2) ON DELETE NO ACTION   'default  
REFERENCES MyTable2(MyColumn2) ON DELETE CASCADE
REFERENCES MyTable2(MyColumn2) ON DELETE SET NULL
REFERENCES MyTable2(MyColumn2) ON DELETE SET DEFAULT

REFERENCES MyTable2(MyColumn2) ON UPDATE


99) Can you describe the 'Not Null' constraint ?

This indicates that the column cannot accept Null values

CREATE TABLE MyTable 
(
    MyColumn varchar(255) NOT NULL
)


100) Can you describe the 'Unique' constraint ?

This indicates that the column must contain nique values.
It will create a non-clustered index automatically ?

CREATE TABLE MyTable 
(
    MyColumn int UNIQUE
)


101) Can you describe the 'Check' constraint ?

This indicates that there is a restriction on the value that can be added.

CREATE TABLE MyTable 
(
    MyColumn int CHECK (MyColumn > 20)
)


102) Can you describe the 'Default' constraint ?

This indicates a default value for this column

CREATE TABLE MyTable 
(
    MyColumn varchar(255) DEFAULT 'text'
)


103) What is a Stored Procedure ?

A stored procedure is a set of pre-compiled SQL statements that are used to perform specific tasks.


104) What are the advantages of using a Stored Procedure ?

Performance gains due to pre-compilation.
Being able to keep statistics on the code to make sure it is optimised.
Removing complexity from the user.
Code can be easily reused.


105) Can you give some examples of the different types of Stored Procedures ?

*) System Stored Procedures - Are used to perform administrative/DBA activities. These are prefixed with sp_ and adding them to the master database makes them available without the need to prefix thm with the name of the database.
*) User Defined Stored Procedure - These are not stored in the master database. These include stored procedures, user defined functions and triggers. Once compiled the details are saved in the following three system tables: sysobjects, sysdepends, syscomments


106) What is a Trigger ?

A trigger is procedural code that is automatically executed in response to certain events on a particular table or view.
They are implicitly executed when you INSERT, UPDATE or DELETE.


107) Can you call a Trigger from inside a stored procedure ?

No. It is event based and can only be executed automatically.


108) What is a Cursor ?

A cursor is associated with a SELECT Query and allows processing one row at a time.

DECLARE MyCursor CURSOR READONLY 
FOR
SELECT
FROM
OPEN
FETCH NEXT FROM MyCursor INTO


109) Can you write the pseudo code for a SQL Server Stored Procedure that returns a SELECT statement ?

CREATE PROCEDURE [dbo].[spMyStoredProcedure] 
   Input [@parameter1] datatype [length]
   Output [@parameter2] datatype [length]
OUTPUT AS Int, Char

BEGIN

//variable declaration
[@parameter1] datatype [length]

//SQL statements
END


110) Can you describe how you would copy the structure of a table without copying the actual data ?



111) What is the DESCRIBE command ?



112) What is Collation ?



113) What is the difference between the DELETE and TRUNCATE commands ?



114) How can you find out how many rows are in a table ?



115) When would you use the UNION ALL statement ?

This combines records from two queries and includes duplicates.
The UNION statement excludes any duplicates.


116) How can you delete a whole table ?

DROP TABLE Customers 


117) How can you change the structure of a table ?

ALTER TABLE Orders 


118) Using the CREATE statement

CREATE TABLE Person( 
    LastName varchar,
    FirstName varchar,
    Address varchar,
    Age int
)


119) Creating an index on a table

CREATE UNIQUE INDEX index_name 
ON table_name (column_name)


120) Using the INSERT statement

The insert statement is used to insert or add a row of data into the table.

INSERT INTO TableName 
(column1, column2, column3)
VALUES (value1, value2, value3)


121) Using the UPDATE statement

UPDATE TableName 
SET TableName.column1 = value1
    TableName.column2 = value2
WHERE TableName.FirstName = "Russell"


122) Using the DELETE statement

This removes all rows in the table which have a specific first name.

DELETE FROM TableName 
WHERE TableName.FirstName = "Russell"


123) What is an Index ?

Filtered index ?


124) What is the difference between a Clustered Index and a Non Clustered Index ?

Clustered indexes are physically aligned with the underlying data representation, allowing ranged queries to be formed efficiently.
Clustered indexes mean that data insertion is slow as the data has to be rearranged.
Non Clustered indexes are independent from the data representation.
Non Clustered indexes are faster ?


125) Is there a maximum number of indexes that you can have on a table ?

You can only have one clustered index.
You can have more than one non-clustered index.


126) What is T-SQL ?

This stands for Transact SQL and is a set of programming extensions that are incoporated into SQL Server.
These are additional features that have been added to provide transaction control, error handling, row processing, variables etc


127) What are DMVs ?

These are Dynamic Management Views that a SQL Server database administrator can use to obtain information about the current state of a database.
These are designed to be used instead of the system tables.
There are two types: server scope and database scope
sys.dm_exec_requests
sys.dm_exec_connections


128) Can you join more than one table ?

SELECT Contracts.ExchName, 
       SUM(Trades.Quantity)
FROM Contracts
INNER JOIN Securities
ON Securities.ContractID = Contracts.ContractID
INNER JOIN Trades
ON Trades.SecurityID = Securities.TradeID
GROUP BY Contracts.ExchName


SELECT Contracts.ExchName, 
       SUM(Trades.Quantity * Trades.Price) / SUM(Trades.Quantity)


129) Display a list of the employee ids that have a total value of the freight shipped that is more than the average for all the employees.

SELECT 
Orders.EmployeeID,
SUM(Orders.Freight)
FROM Orders
GROUP BY Orders.EmployeeID
HAVING SUM(Orders.Freight) > (
      SELECT AVG (t_SubTable.c_Values)
      FROM ("
          SELECT SUM(t_SubOrders.Freight) AS c_Values"
          FROM Orders t_SubOrders"
          GROUP BY t_SubOrders.EmployeeID"
          ) AS t_SubTable
      )



© 2017 Better Solutions Limited. All Rights Reserved. © 2017 Better Solutions Limited

Prev