SQL Fundamentals

Microsoft Enterprise Consortium SQL Fundamentals Introduction Microsoft Enterprise Consortium: http://enterprise.waltoncollege.uark.edu Microsoft Faculty Connection/Faculty Resource Center http://www.facultyresourcecenter.com

What you’ll need … • This is the first in a series of presentations about SQL fundamentals. • For the remaining presentations in this series, you will need a user account from the Microsoft Enterprise Consortium. Teachers can request these accounts for students. • The MEC URL is provided at the beginning and end of this presentation.

Structured Query Language • Structure Query Language - SQL • a.k.a. SQL—say each letter or the word “sequel” • The core SQL commands are consistent across most DBMS software. • The American National Standards Institute (ANSI) sets standards for SQL compliance. • Each DBMS, however, does have some SQL variations. • If you write an SQL script—a set of statements or commands—for one DBMS, it is possible that it will not run in another DBMS without some revision.

Transact-SQL & MSSMS • The SQL used in this series will be either in a generic form compatible with most DBMSs or in Transact-SQL, which is specifically for Microsoft’s SQL Server. • T-SQL – This shorthand for Transact-SQL may be used in this series of lessons. • Microsoft SQL Server Management Studio – This is the software used to connect to SQL Server and provides interfaces for viewing & creating databases, writing & executing SQL, and many other tools. MSSMS may be used as an abbreviation.

SQL – Subsets of statements • Data Definition Language (DDL) • The set of SQL statements used to define objects in the database. • For example, the statement to create a table is a DDL statement. Creating objects such as a table, view or index are DDL statements. • Data Manipulation Language (DML) • The set of SQL statements used to work with the database in a database. • For example, the statement to retrieve rows from a customer table, update a customer’s phone number or add a new customer are DML statements.

Data Definition Language (DDL) • The DDL is a subset of SQL statements used to create and maintain database objects such as tables. • CREATE TABLE • Below is an example of a CREATE TABLE statement that creates a TEAMS table with: • 2 required columns, • 1 optional column • A primary key constraint, and • A unique constraint. CREATE TABLE teams ( teamid VARCHAR(8) NOT NULL, team_name VARCHAR(30) NOT NULL, project VARCHAR(45), CONSTRAINT pk_teamid PRIMARY KEY (teamid), CONSTRAINT uniq_team UNIQUE (team_name) );

DDL – CREATE TABLE • Each column must be assigned a data type. • VARCHAR– This data type is used for variable width alphanumeric fields. The width should be defined. • Use NOT NULL to define whether a column must have data or not. CREATE TABLE teams ( teamid VARCHAR(8) NOT NULL, team_name VARCHAR(30) NOT NULL, project VARCHAR(45), CONSTRAINT pk_teamid PRIMARY KEY (teamid), CONSTRAINT uniq_team UNIQUE (team_name) );

DDL – Constraints • CONSTRAINT … PRIMARY KEY – This constraint clause lists which field(s) is the primary key. Every table should have a primary key. • In this example, teamid is the primary key. • PK_TEAMID is the name of the constraint. • CONSTRAINT … UNIQUE – Even though team_name is not the primary key, you can still define the column as unique. • No two teams can have the same name in this table. CREATE TABLE teams ( teamid VARCHAR(8) NOT NULL, team_name VARCHAR(30) NOT NULL, project VARCHAR(45), CONSTRAINT pk_teamid PRIMARY KEY (teamid), CONSTRAINT uniq_team UNIQUE (team_name) );

DDL – Foreign Key • This CREATE TABLE statement below shows how a foreign key constraint is defined. • CONSTRAINT … FOREIGN KEY … REFERENCES • FK_TEAMID is the name of the constraint. • STD_TEAMID is the field name of the foreign key. • The foreign key field references the primary key in the TEAMS table. Notice that the primary key field in TEAMS is not listed. It was already defined when the TEAMS table was created. CREATE TABLE students ( stdid VARCHAR(10) NOT NULL, stdfname VARCHAR(12) NOT NULL, stdlname VARCHAR(13) NOT NULL, stdmajor VARCHAR(6), std_teamID VARCHAR(8), CONSTRAINT pk_stdid PRIMARY KEY (stdid), CONSTRAINT fk_teamid FOREIGN KEY (std_teamid) REFERENCES teams );

Relational Model – Creating relationships in the database • A data model shows the sequence in which tables are created in the database. • In a one-to-many relationship, the table on the “one” side is created first so that it already exists when the foreign key constraint is created in the “many” table. TEAMS STUDENTS EVAL_ITEMS TEAMS is created before STUDENTS. STUDENTS is created before EVALUATIONS. EVALUATIONS EVAL_ITEMS_SCORES EVAL_ITEMS and EVALUATIONS are created before EVAL_ITEMS_SCORES.

Databases for this SQL series • For this series of lessons and exercises, we will start with these databases. • AdventureWorks Light & Adventure Works • These are existing databases available through your MEC account. • Student-Teams • You will create this database from SQL scripts provided. There are 3 script files. • A file to create 5 tables. • A file to insert data into the tables. • A file to drop all the tables, if needed. • You will probably only use the first two files but the third file enables you to start over, if you need to. • There is a PowerPoint show and video that walks through the first two steps.

What was covered … • ANSI • Transact-SQL (T-SQL) • Microsoft SQL Server Management Studio (MSSMS) • SQL • DDL • DML • DDL • CREATE TABLE • Define columns • Define constraints • Databases used in this SQL series • AdventureWorks (pre-existing) • Student-Teams (to be created)

Resources • http://enterprise.waltoncollege.uark.edu/mec.asp • Microsoft Faculty Connection—Faculty Resource Center http://www.facultyresourcecenter.com/ • Microsoft Transact-SQL Reference • http://msdn.microsoft.com/en-us/library/aa299742(v=SQL.80).aspx

SQL Fundamentals