Development SQL Server Databases.txt

Card Set Information

Development SQL Server Databases.txt
2014-04-19 16:46:59
Software Development SQL Server Databases
Software Development SQL Server Databases
Software Development SQL Server Databases
Show Answers:

  1. What versions of SQL Server have you worked with?
    2005, 2008, 2008(R), 2010, and 2012.
  2. What's the difference between pessimistic and optimistic locking?
    When an update to a record is done, a lock is placed on the transaction for updating, if the lock is on for the entire transaction, then it is pessimistic, if only for the brief time for the update within the transaction, then it is optimistic locking; ASP.NET applications almost always use optimistic locking whereas an ATM machine would always use pessimistic locking.
  3. What are the names of the various system databases in SQL Server? Explain primary function of each.
    MASTER (Contains all the system level information for SQL such as logins, servers, configuration settings); MODEL (This is a template DB wherein all other new databases will model, guaranteeing items like rules, SP, users, and more without having to re-create these each time); MSDB (Primary database to manage the SQL Server Agent, configurations, Mail, Backup History, DTS packages, Agent jobs/alerts, SSIS packages); TEMPDB (Temporary database to store temporary tables, variables, cursors, and more and to rebuild indexes sorted in TempDB each time the query analyzer is used. Each time the SQL Server instance is restarted all objects in this database are destroyed since the workhorse DB is essentially a scratch-pad); RESOURCES (a hidden system database where system objects are stored for DB manipulation ONLY)
  4. Give a list of 10 common SQL functions.
    Min(), Max(), Sum(), UCase(), Len(), Mid(), Now(), Round(), Count(), Last()
  5. What is a primary key?
    The PRIMARY KEY constraint uniquely identifies each record in a database table, it must contain a unique value AND can not contain NULL values, and each table can ONLY have one primary key.
  6. What is a foreign key used for?
    Maintaining referential integrity, that is, making sure that any value in one table matches another value in a different table.
  7. What does it mean to normalize a database?
    Normalization is the process of efficiently organizing data so that (a) eliminate redundancy and (b) ensuring data dependencies exist between tables. Normalization typically creates more tables and relates those tables using relationships. For example, think of a single Excel spreadsheet containing customer information along with amounts and dates a purchase was made...a normalization would result in two tables being created (Customers, Orders) along with a relationship between them.
  8. Normalize the following table, using the column names as a guide: Customer, Date, Item, Price
    Result would include two tables (Customers, Orders) and placing Customer in one table with other items in the Orders table.
  9. What are the benefits or normalization?
    (a) eliminate redundancy, (b) improve performance, (c) query optimization, (d) less number of columns to search,
  10. What is the format of Date()? DateTime()?
    YYYY-MM-DD and YYYY-MM-DD HH:MM:SS, respectively
  11. What's a clustered index?
    A clustered index stores the actual data rows at the leaf level of the index. Returning to the example above, that would mean that the entire row of data associated with the primary key value of 123 would be stored in that leaf node. An important characteristic of the clustered index is that the indexed values are sorted in either ascending or descending order. As a result, there can be only one clustered index on a table or view. In addition, data in a table is sorted only if a clustered index has been defined on a table.
  12. A table that has a clustered index is referred to as a XXX. A table that has no clustered index is referred to as a XXX.
    Clustered table; Heap.
  13. What's a non-clustered index?
    Unlike a clustered indexed, the leaf nodes of a non-clustered index contain only the values from the indexed columns and row locators that point to the actual data rows, rather than contain the data rows themselves. This means that the query engine must take an additional step in order to locate the actual data. In addition, data in a table is NOT sorted if it is non-clustered.
  14. What is an index?
    One of the most important routes to high performance in a SQL Server database is the index. Indexes speed up the querying process by providing swift access to rows in the data tables, similarly to the way a book’s index helps you find information quickly within that book. An index can be placed on one or more columns in a table. Two types of index exist: Clustered (value) and Non-Clustered (reference).
  15. What's the difference between a primary key, foreign key, and an index?
    Primary key is a unique key that is not null; foreign key is a key whose primary key is in another table; an index is like a lookup key, which can be placed on one or more columns within a table for faster searches.
  16. What's the difference between the DELETE and TRUNCATE command?
    TRUNCATE TABLE is a statement that quickly deletes all records in a table by deallocating the data pages used by the table. This reduces the resource overhead of logging the deletions, as well as the number of locks acquired; however, it bypasses the transaction log, and the only record of the truncation in the transaction logs is the page deallocation. Records removed by the TRUNCATE TABLE statement cannot be restored. You cannot specify a WHERE clause in a TRUNCATE TABLE statement-it is all or nothing. The advantage to using TRUNCATE TABLE is that in addition to removing all rows from the table it resets the IDENTITY back to the SEED, and the deallocated pages are returned to the system for use in other areas. DELETE TABLE statements delete rows one at a time, logging each row in the transaction log, as well as maintaining log sequence number (LSN) information. Although this consumes more database resources and locks, these transactions can be rolled back if necessary.
  17. Compare an aggregate function to a scalar function.
    One works on multiple items whereas the other on a single it, such as AVG() and MAX vs UCASE() and NOW().
  18. What is a user defined function?
    Two types exist: in-line functions which return multiple records or a scalar function which returns a single value.
  19. What is a trigger?
    A special kind of stored procedure that executes in response to a certain action (ie, insertion or deletion of a record).
  20. What are some of the benefits of using a stored procedure?
    (a) code reuse, (b) code tested for errors prior to be saved, compiled, (c) compiled, so runs faster, (d) security dur to encryption, (e) data encapsulation since only the interface is exposed in the form of parameters.
  21. Give several examples of constraints.
    Constraints are limitations set forth on a column, that the constraint must be satisfied in order for a record to exist in that table. They include primary key, foreign key, unique key, not null, check, and default value.
  22. What's the difference between a view and a stored procedure?
    (a) SP are compiled, (b) SP contain parameters, (c) no updates to DB allowed in views, such as Insert, Delete, Update, (d) views are simplistic in that the can contain only a single SELECT query whereas a SP can contain complex logic such as IF/ELSE, CASE WHEN, or DO WHILE, (e) SP cannot be used as a building block for larger queries but views can (exception is that a stored procedure can be called within another stored procedure).
  23. What is a view?
    Views are virtual tables compiled at run time and the data associated with the view is not physically stored in the view, rather only the definition of the view is stored in the database (unless used with an index), that is, the metadata for the view is stored for later use. Views can contain data from various tables that we would like to query over and over again. Once created, you would treat a view like a table. For security purposes, usually a view is provided rather than a table since you can abstract which columns you want the client to see.
  24. Name some common SQL Data Types.
  25. What can a bit hold?
    0, 1, or null
  26. What is a check?
    A constraint type that makes sure a value is one that falls within the constraint, For example, that values be either 1,2, or 3 would be a check constraint.
  27. Write a query to update ID = 47 with the name 'Peter'.
    UPDATE Leads SET firstname = 'Peter' WHERE id = 47;
  28. Write a query to insert an item into the database.
    INSERT INTO Customers (CustomerName, City, Country) VALUES ('Cardinal', 'Stavanger', 'Norway');
  29. What's the difference between CHAR, VARCHAR, and TEXT. Explain.
    Char(n) hold a fixed length character, VarChar(n) is dynamic and can hold up to n maximum (n can't exceed 8k characters), Text can hold up to 1 billion characters.
  30. Name the different types of JOINS.
    INNER, LEFT, RIGHT, CROSS, and FULL OUTER. The Left and Right Joins are considered outer joins, and the inner join is the default join, meaning a query that just uses the join keyword is using the inner join.
  31. What occurs if you return a LEFT JOIN that doesn't match the contents in the right table?
    Write the two different query styles to pull records from two tables.
  32. Write a simple inner join.
    SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate FROM Orders INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID;
  33. What's the difference between ISNULL() function and using IS NULL in a statement?
    ISNULL() is a function, it takes two parameters, the first is the item to check and the second it is the thing to substitute if the value being checked is null. The IS NULL statement is used to check a condition for those in the WHERE clause of TSQL.
  34. What's the difference between Unique and Distinct keywords?
    Unique is used for the creation of a table column to show that all items are unique, whereas Distinct keyword is used in the querying of data to return only distinct items.
  35. What is the difference between a UNIQUE and PRIMARY key?
    Both must be unique in their column, but only one primary can exist on a table whereas a single table may contain numerous unique keys.
  36. What is the COALESCE function used for in TSQL?
    The function returns the first non-null value in the list.
  37. Use COALESCE in a query.
    SELECT Id, COALESCE(FirstName,MiddleName,LastName) AS Name FROM tblEmployee
  38. Write a query that checks for several items in the state column using the IN keyword.
    SELECT * FROM States WHERE State IN ('CA', 'NY', 'GA');
  39. Write a query to return values between two different dates.
    SELECT * FROM Orders WHERE OrderDate BETWEEN #07/04/1996# AND #07/09/1996#;
  40. Write a simple script to create a Lead table with a primary key and several common columns.
    CREATE TABLE Persons( ID int NOT NULL PRIMARY KEY, Name VARCHAR(255) NOT NULL, Address VARCHAR(255), City VARCHAR(255), State CHAR(2), Zip CHAR(5), Phone INT, DOB DATE, Sex BIT)
  41. Write a simple query that uses CASE WHEN statement.
    SELECT CASE WHEN MIN(value) <= 0 THEN 0 WHEN MAX(1/value) >= 100 THEN 1 END FROM Data;
  42. Write code to do a simple transaction.
    BEGIN @MyTransaction INSERT INTO #Table1 values (3); END IF @@ERROR <> 0 ROLLBACK @MyTransaction ELSE COMMIT TRANSACTION @MyTransaction
  43. How do you return a successful or failed response to the user?
  44. Define an Inner Join
    The INNER JOIN keyword selects all rows from both tables as long as there is a match between the columns. If there are rows in the "Customers" table that do not have matches in "Orders", these customers will NOT be listed.
  45. Define a LEFT JOIN:
    Return all rows from the left table, and the matched rows from the right table; if no match exists in the right table, then null is used in place
  46. Define a RIGHT JOIN:
    Return all rows from the right table, and the matched rows from the left table; if no match exists in the left table, then null is used in place
  47. Define a FULL OUTER JOIN:
    The FULL OUTER JOIN keyword returns all the rows from the left table (Customers), and all the rows from the right table (Orders). If there are rows in "Customers" that do not have matches in "Orders", or if there are rows in "Orders" that do not have matches in "Customers", those rows will be listed as well.
  48. Can a LEFT JOIN query contain more record results than exist in the left table?
    Yes, in fact, they often do. For example, a Customer table containing just two customers could be queried against a Orders table containing hundreds of orders, the LEFT JOIN on such a table could easily contain numerous records since each customer is likely to have numerous orders each.
  49. Could a LEFT JOIN query contain less record results than exist in the left table?
    No, because at the VERY least, it would contain the same number of records as that of the left table...and that would only happen if no matches were found in the right table.
  50. What's the criteria for a UNION query?
    Both tables must have (a) same number of columns, (b) same name of columns, (c) same data types for each column, and (d) same order of each column
  51. What's the difference between a UNION and UNION ALL SQL Statement?
    The UNION statement returns as DISTINCT records; however, the UNION ALL returns all records, even if they are duplicates.
  52. Write a query to put the contents of all California Leads into a new table called CalLeads.
    SELECT INTO CalLeads FROM Leads WHERE State = 'CA';
  53. What is a SELECT INTO query used for?
    The SELECT INTO statement copies data from one table and inserts it into a new table.
  54. What is the INSERT INTO SELECT statement used for? Give an example.
    The INSERT INTO SELECT statement selects data from one table and inserts it into an existing table. Any existing rows in the target table are unaffected. Example: INSERT INTO table2 SELECT * FROM table1;
  55. Write a simple query to determine the number of days between your birthday and today.
    SELECT DATADIFF(birthday, NOW());
  56. How can we test for null values in a column?
    We can't use comparison operators such as >, <, >=, <=, or =...for example, we can't say, SELECT * FROM Leads WHERE Address <> NULL; Instead, we can ONLY check for null values using the IS NULL or IS NOT NULL expression.
  57. Why was the HAVING clause created?
    The HAVING clause was added to SQL because the WHERE keyword could not be used with aggregate functions.
  58. Write a simple query to determine if any Customers have more than 10 Orders in the Customers, Orders tables.
    SELECT Employees.LastName, COUNT(Orders.OrderID) AS NumberOfOrders FROM Orders INNER JOIN EmployeesON Orders.EmployeeID = Employees.EmployeeID GROUP BY LastName HAVING COUNT(Orders.OrderID) > 10;
  59. What are the trade-offs with having indexes?
    (a) Faster selects, slower updates. (b) Extra storage space to store indexes. Updates are slower because in addition to updating the table you have to update the index
  60. Write a simple create stored procedure script called usp_GetCaliforniaLeads and retrieve the first 10 records.
    CREATE PROCEDURE sp_myStoredProcedure @number int, @state char(2) AS SELECT TOP @number * FROM Leads WHERE State = @state; Go
  61. Write a simple create view script called udv_GetCaliforniaLeads.
    CREATE VIEW udv_GetCaliforniaLeads AS SELECT * FROM Leads WHERE State = 'CA';
  62. Write a simple create Leads table script containing a primary key, index, a non-null column(s), a default timestamp, and.
    CREATE TABLE Leads ( id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, name VARCHAR(100), address VARCHAR(100), city VARCHAR(50), state CHAR(2), zipcode CHAR(10), phone INT NOT NULL, dob DATE NOT NULL, created DATETIME DEFAULT NOW() );
  63. What is an execution plan?
    SQL Server Management Studio provides the Database Administrator or programmer the ability to manipulate DB objects (ie, tables, views, constraints, and more) and use the query analyzer to run queries against those objects. Rather than simply seeing the results of the query, the user may request to "Show Execution Plan" which will show how the SQL Query Optimizer will run the query and provide a graphical representation of that execution plan and estimated times and processing statistics.