User FAQs


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) 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


4) What is a Query ?
A query is a request for some data from one or more tables using the SELECT statement.


5) 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 
)

6) Can you describe the different types of SubQueries in SQL Server ?
*) Single Row
*) Multiple Row
*) Multiple Column


7) 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 ?


8) 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.


9) 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.


10) 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.


11) 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

12) 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


13) 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

14) 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


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


16) 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.


17) How do you concatenate columns ?

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

In Access you can also use ampersand (&).


18) Can you use any Date Literal Constants ?

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

19) 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.


20) 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

21) Are the Data Types the same in Access and SQL Server ?
No. They are very different.

ANSISQL ServerAccess
??????

22) 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 -

23) 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) -

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

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

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

DATE - 
TIME - 
TIMESTAMP - 
INTERVAL -

26) 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


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



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



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



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



31) 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 ?


32) 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


33) 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

34) 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)


35) 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

36) 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'

37) Can you describe the ANY operator ?
Not available in Access. Only SQL Server.
This is exactly the same as SOME.


38) 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

39) 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.


40) 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')

41) Can you describe the IS NULL operator ?
??

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

42) 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]%'

43) 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.


44) 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 -


45) 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'

46) Can you describe the OR operator ?
This can be used to only display a row if either of the conditions are True.

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

47) 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.


48) How do you select everything from a table ?

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

49) 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

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

SELECT TOP 5 
       Customers.CompanyName 
FROM Customers

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

SELECT TOP 10 PERCENT 
       Customers.CompanyName 
FROM Customers

52) Can you describe the ORDER BY clause ?
??

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

53) Write a query that gets employees that have a salary greater than 5000 without using the WHERE clause ?
HAVING with no GROUP BY



54) 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.


55) 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.


56) 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

57) 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.


58) 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.


59) 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.


60) 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.


61) 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.


62) 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

63) 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 ?


64) 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


65) 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.


66) 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

67) 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

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

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

69) 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

70) 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

71) 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

72) 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

73) 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)

74) 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.


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

SELECT NOW       'Access only  
SELECT GETDATE   'SQL Server only

76) How do you replace characters ?
There is no REPLACE function when you use OLEDB

SELECT REPLACE   'Access sometimes  
SELECT REPLACE   'SQL Server

77) 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

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

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

79) 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.


80) 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


81) 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.


82) 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 
)

83) 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) 
)

84) 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

85) Can you describe the 'Not Null' constraint ?
This indicates that the column cannot accept Null values

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

86) 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 
)

87) 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) 
)

88) Can you describe the 'Default' constraint ?
This indicates a default value for this column

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

89) What is a Stored Procedure ?
A stored procedure is a set of pre-compiled SQL statements that are used to perform specific tasks.


90) 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.


91) 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


92) 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.


93) Can you call a Trigger from inside a stored procedure ?
No. It is event based and can only be executed automatically.


94) 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

95) 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

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



97) What is the DESCRIBE command ?



98) What is Collation ?



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



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



101) When would you use the UNION ALL statement ?
This combines records from two queries and includes duplicates.
The UNION statement excludes any duplicates.


102) How can you delete a whole table ?

DROP TABLE Customers

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

ALTER TABLE Orders

104) Using the CREATE statement

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

105) Creating an index on a table

CREATE UNIQUE INDEX index_name 
ON table_name (column_name)

106) 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)

107) Using the UPDATE statement

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

108) Using the DELETE statement
This removes all rows in the table which have a specific first name.

DELETE FROM TableName 
WHERE TableName.FirstName = "Russell"

109) What is an Index ?
Filtered index ?


110) 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 ?


111) 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.


112) 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


113) 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


114) 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)

115) 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 
      )


© 2024 Better Solutions Limited. All Rights Reserved. © 2024 Better Solutions Limited TopPrevNext