Prep Database SQL Server Queries

• Seven Venn diagrams each, represented by:
• 1. SELECT * FROM A LEFT JOIN B ON A.KEY = B.KEY
• 2. SELECT * FROM A RIGHT JOIN B ON A.KEY = B.KEY
• 3. SELECT * FROM A LEFT JOIN B ON A.KEY = B.KEY AND B.KEY IS NULL
• 4. SELECT * FROM A RIGHT JOIN B ON A.KEY = B.KEY AND A.KEY IS NULL
• 5. SELECT * FROM A INNER JOIN B ON A.KEY = B.KEY
• 6. SELECT * FROM A FULL OUTER JOIN B ON A.KEY = B.KEY 
• 7. SELECT * FROM A FULL OUTER JOIN B ON A.KEY = B.KEY AND A.KEY IS NULL OR B.KEY IS NULL

An inner join is one in which both tables share a common key match and so those record rows are returned. In a left join, all records in the left table are returned, even if no matches on the key is found.

A primary key is one which is unique and used to describe a single record. Almost always it is indexed for fast retrieval. A foreign key is a key reference to a primary key within another table. A composite key is a key which requires two or more columns to define the key.

SQL is a relational database created by Microsoft. Others include DB2, Oracle, and mySQL

Int, char, varchar, bit, binary, blob, nchar, datetime, timestamp, decimal, text, Boolean, float, double, date, time, year

Two types exist, clustered and non-clustered. Only one clustered can exist on a table which is a valued type, usually the primary key and usually in succession (ordered). The non-ordered indexes are reference types and are not as quick as a clustered but are often used for quick retrieval, updates, etc.

Normalization is the process of massaging the data to improve data integrity and eliminate redundancies. Denormalization is the flattening of the data and is almost exclusively used in reporting as it produces very quick results without having to reference additional tables.

Database objects are objects located directly within the database; examples can include procedural database objects which are stored procedures, user defined functions, and triggers but not scripts as these are external to the application itself.

An event which is placed on an INSERT, DELETE, or UPDATE

An INNER JOIN is one which both sides of the table meet the condition. A LEFT JOIN is one in which all elements in the left side are returned and those in the right are either present or displayed as NULL values. RIGHT is same as LEFT but reversed. Two others are used, albeit infrequently, including CROSS JOIN AND UNION/UNION ALL.

Custom function which takes 0-n arguments and returns either a single scalar value or a table value. Some characteristics is that it cannot call a stored procedure nor do any modification changes to the database such as an INSERT or DELETE.

Custom database object which is compiled and stored in the database for fast execution. Unlike the function, it isn’t compiled on the fly, it can take 0-n arguments, and may or may not return a scalar value or table. In addition, it contain complex flow logic, perform modification changes to the database, can call other stored procedures, can contain error handling, transaction processing, and more.

An Index is a structural mechanism which allows easy, quick retrieval of records whereas a key is a record identifier.

Filtering data such as column constraints work on the columns themselves, however, SQL Injection attacks should be doing filtering at the client level by preventing dynamic SQL calls and the use of stored procedures to make sure data types and proper values are being sent to the user.

LTRIM(), TRIM(), LEN(), RIGHT(), SUBSTRING(), REPLACE(), REVERSE(), INDEX(), CONCATE(), COALESCE(), ISDATE(), ISNULL(), LOWER(), UPPER(), DAY(), MONTH(), YEAR(), MIN(), MAX(), FLOOR()

Column generated by the database itself and may or may not be unique. Whereas a Primary Key is a unique identifier, an identity can be one type of primary key, that is, the identity column may not be automatically generated from the database but input by the user such as a SSN would.

• SELECT 
•      ID,
•      CASE VENDORID
•           WHEN 1 THEN ‘A’
•           WHEN 2 THEN ‘B’
•           ELSE ‘C’
•      END AS VENDORCHECK
• FROM LEADS

BEGIN TRY BEGIN TRAN (….) COMMIT TRAN END TRY BEGIN CATCH ROLLBACK TRAN END CATCH

• /* Use # before LEAD name for local temp table and ## for a global temp version */
• CREATE TABLE LEAD
• (
•    ID INT NOT NULL IDENTITY PRIMARY KEY,
•    NAME VARCHAR(30) NOT NULL,
•    SEX CHAR(1) NOT NULL DEFAULT ‘M’,
•    DOB DATE,
•    CREATED DATETIME NOT NULL DEFAULT GETDATE()
• )

SELECT NAME, COUNT(NAME), SUM(Price) FROM INVOICE GROUP BY Name HAVING SUM(Price) > 0 ORDER BY Name ASC;

SELECT L.Name AS ‘Client Name’, I.InvoiceId AS ‘Invoice #’ FROM Leads L INNER JOIN INVOICE I ON L.InvoiceId = I.Id;

UPDATE Leads SET Name = ‘Frank’ WHERE Id = 313;

INSERT INTO Leads (name, dob) VALUES (‘Frank’, ‘1971-10-1’);

INSERT INTO Leads (name, dob) VALUES (‘Frank’, 2), (‘Dorthy’, 3);

DELETE FROM Leads WHERE id = 31;

• DROP TABLE Leads; TRUNC TABLE Leads;
• The difference is that dropping a table updates the log with every change so on very large tables this can take hours to delete and update every record whereas on a truncate, the log isn’t updated, the pointers are just removed and so this takes a millisecond. It is very dangerous to do a truncate as it can’t be recovered from; however, it is useful depending on the circumstance.

SELECT TOP 5 DISTINCT name FROM Leads WHERE SUM(sales) > 500;

• WHERE DOB BETWEEN ‘2017-01-01’ AND ‘2018-12-31’
• SELECT GETDATE();

DECLARE @firstname AS VARCHAR(30) ;

• SELECT Name WHERE name LIKE ‘M__%’;
• /* The % sign is used as a wild card meaning many and the underscore is used to refer to any one character.

SELECT * INTO LeadsCopy FROM Leads;

• SELECT ‘The children are ‘ + cast(age as varchar) + ‘ years old.’;
• SELECT ‘The children are ‘ + convert(varchar, age) + ‘years old.’;

A > B; A <= B; A = B; A <> B;

SELECT ID, NAME FROM LEADS WHERE MODIFIED IS NULL;

SELECT name, datediff(day, dob, getdate()) as ‘ days old’ FROM Leads;

One-to-one (each student should have one address), one-to-many (each student can have multiple teachers or multiple report cards), and many-to-many (each student can have multiple classes and each class can have multiple students).

The IS NULL condition refers to a column which contains a NULL value. If we return a data set with all values greater than zero and a NULL value is present in this field as opposed to 0.00 then it wouldn’t be returned, to include it we would use ‘OR IS NULL’ expression. ISNULL() is a function which takes two required parameters of the form ISNULL(expression, value) which means to test the expression and if it contains something, then return it, otherwise if null, return the value in the value area.

Parameters with a default value of NULL are optional parameters meaning it may or may not be sent.

EXEC spListOfVendors ‘USA’, 5000;

SELECT Name, Id, dbo.udfGetAge(Id);

• THROW 50001, ‘Not a valid Id’, 1;
• We can check by using a TRY CATCH block or by using the @@ERROR <> 0 or @@Identity check.

• Write code to check for an error and write a message to user.
• IF @@ERROR <> 0
•    BEGIN
•       PRINT N’An error occurred: ‘ + ERROR_MESSAGE();
•       RETURN 0;
•    END
• GO

Several types of errors can exist when two individuals try to modify data in the database concurrently and these create concurrency issues. To prevent this, an administrator typically changes the locking mechanism behavior of the database as a whole coupled with programmers who write transactional updates to the system. Doing this prevents the four types of concurrency errors including phantom reads, nonrepeatable reads (on datasets), dirty reads, and lost updates.

A view is a virtual table in that the data is not stored directly like a table but is still accessible. It can be used to restrict access (subset of a table) or to abstract complexity (combining of multiple tables) while also offering a bit of security.

Yes… simply by returning the results of a query. SELECT id, name FROM Leads WHERE dob >= ‘2000-01-01’;

jQuery112409895917431100423_1606929746416?

???

???

Every table can include multiple indexes, however, only one type of clustered index can exist on a table as this is the index used to refer to a specific (value type) record. Non-clustered indexes are reference types which contain pointers to their value thus take longer to process.

Most common is to add a clustered index (ie, primary key) to a table followed by one or more non-clustered indexes for those columns which are commonly referenced in queries. A candidate for consideration for an index column would be one which is (a) frequently called, (b) contains many distinct values, or (c) is updated frequenty.

The MDF file is created which contains the actual data and an LDF file is created which will contain all the changes as it is a log file of every change to the database.

Scripts are created for extra large jobs or to run batch jobs done at night, etc.

SELECT * INTO #LeadsCopy FROM Leads; /* The table exists as long as the current local session is active */

EXEC (‘SELECT * FROM LEADS;’);

Occurs when two users try to simultaneously access the same record by placing a lock on the other party, yet, both are waiting for the other and so neither can complete their action.