SQL Exam

SQL_exam.md

SQL exam questions

• Relational Database Concepts

  • Relational database?
  • Normalization
  • Elements (Table, Index, Key, Constraint, View, Stored Procedure, Trigger)
  • SQL Language (DDL, DML, DCL, DCL, TCL)
  • Transactions (Atomicity, Consistency, Isolation, Durability)

• Creating SQL Server Databases

  • Name
  • Owner
  • File, filegroup
  • Collation
  • RecoveryModel
  • Syntax

  CREATE DATABASE JoBS ON PRIMARY (NAME='JoBSData', FILENAME='c:\JoBS.mdf') LOG ON (NAME='JoBSLog', FILENAME='c:\JoBS.ldf') COLLATE Latin1_General_CI_AS

• Data types

  • Numeric
  • Character
  • Text
  • Datetime
  • Binary

• Creating SQL Server Tables

  CREATE TABLE GeographicalAreas ( AreaCode CHAR(3) NOT NULL, AreaName VARCHAR(25) NOT NULL ) ON METADATA

• Basic SQL Server Data Manipulation (DML)

  • Insert

    INSERT INTO Parts (PartNumber, PartName, Cost) VALUES ('22COPIPE', '22mm Copper Pipe', 2.97)

  • Update

    `UPDATE Engineers`
        `SET HourlyRate = HourlyRate + 0.25`
    `WHERE HourlyRate >= 29.50`
    

  • Delete

    DELETE FROM Engineers WHERE HourlyRate > 29.50

• Basic T-SQL Queries

  • Select all/some fields

  • Column aliases

  • TOP/PERCENT/WITH TIES

  • DISTINCT

  • Expressions in collumn def

  • Filtering

    • Relative expression
    • IN/NOT IN
    • BETWEEN
    • LIKE
    • IS NULL/IS NOT NULL
    • AND/OR/NOT

  • Sorting

    • DESC/ASC

• Modifying SQL Server Tables

  • Add column
  • Alter column
  • Drop column
  • Rename column
  • Drop table

• Primary Keys

  • Natural/Surrogate
  • Simple/Clustered

• Foreign Keys

  • One-to-many/many-to-many
  • Cascading Updates and Deletes

• Unique Constraints

• Default Column Values

• Identity Columns

• Computed Columns

• SQL Server Indexes

  • Clustered
  • Non-Clustered
  • Unique
  • with Included Columns

  CREATE INDEX -- Non-clustered

  CREATE NONCLUSTERED INDEX -- Non-clustered

  CREATE CLUSTERED INDEX -- Clustered

  CREATE UNIQUE INDEX -- Unique, non-clustered

  CREATE UNIQUE CLUSTERED INDEX <IndexName> ON <tableName> INCLUDE <columns>

• Database Normalization

  • 1 Normal form (NF)

    • no any specific ordering.
    • no duplicate rows.
    • Every column of every row must contain exactly one element of data.
    • no hidden information in rows

  • 2 NF

  same with

    - all non-prime columns, must depend upon entire candidate keys, not just parts of those keys
  

  • 3 NF

  same with

    - all non-prime columns must be directly dependant upon the candidate keys
  

• Transact-SQL Joins

  • INNER JOIN (JOIN)

    • Implicit/Explicit syntax

  • join multiple tables

  • LEFT/RIGHT OUTER JOIN (LEFT/RIGHT JOIN)

  • CROSS JOIN (Cartesian join)

  • Table aliases

• Using Transactions in SQL Server

  • ACID

    • Atomicity
    • Consistency
    • Isolation
    • Durability

  • BEGIN/COMMIT/ROLLBACK

  • Locking

• Views

  CREATE VIEW PartList AS SELECT PartNumber, PartName FROM Parts

  • Schema binding

  CREATE VIEW SingleJobs WITH SCHEMABINDING AS

  • Indexed Views

• Check Constraints

  • NOCHECK option
  • NOT FOR REPLICATION option
  • Disable CHECK constraint
  • Enable/enable WITH CHECK

• Union Operations

  • UNION/UNION ALL

• Except and Intersect

  • EXCEPT
  • INTERSECT

• Basic Aggregation Functions

  • count()/count_big()
  • sum()
  • avg()
  • min()/max()

• Grouping

  • GROUP BY/GROUP BY ALL
  • HAVING
  • WITH ROLLUP
  • WITH CUBE
  • grouping() function

• Subqueries

  • Standart/Correlated subqueries
  • EXISTS/IN/NOT IN

• Stored Procedures

  • Return value
  • Parameters/default values/Output parameters
  • ALTER/DROP PROCEDURE

• Control-of-flow

  • BEGIN..END block
  • IF..ELSE condition
  • CASE..WHEN condition
  • WHILE..BREAK loop

• Functions

  • Datetime Functions
  • Other functions

• Triggers

  • DML triggers

    • FOR/AFTER/INSTEAD OF INSERT/UPDATE/DELETE
    • deleted/inserted tables
    • update()/columns_updated() functions

  • DDL triggers

    • Database events
    • eventdata() function