Structured Query Language - University College of Southeast Norway Structured Query Language Hans-Petter Halvorsen 2016.01.08 The Tutorial is available

Structured Query Language - University College of Southeast...

This preview shows page 1 - 7 out of 78 pages.

University College of Southeast Norway Structured Query Language Hans-Petter Halvorsen, 2016.01.08 The Tutorial is available Online:
Image of page 1
2 Table of Contents 1 Introduction to SQL ........................................................................................................... 5 1.1 Data Definition Language (DDL) .................................................................................. 7 1.2 Data Manipulation Language (DML) ........................................................................... 7 2 Introduction to SQL Server ................................................................................................ 8 2.1 SQL Server Management Studio ................................................................................. 9 2.1.1 Create a new Database ...................................................................................... 10 2.1.2 Queries .............................................................................................................. 11 3 CREATE TABLE ................................................................................................................. 12 3.1 Database Modelling .................................................................................................. 14 3.2 Create Tables using the Designer Tools .................................................................... 16 3.3 SQL Constraints ......................................................................................................... 16 3.3.1 PRIMARY KEY ..................................................................................................... 17 3.3.2 FOREIGN KEY ..................................................................................................... 18 3.3.3 NOT NULL / Required Columns ......................................................................... 21 3.3.4 UNIQUE ............................................................................................................. 22 3.3.5 CHECK ................................................................................................................ 24 3.3.6 DEFAULT ............................................................................................................ 26 3.3.7 AUTO INCREMENT or IDENTITY ......................................................................... 27 3.4 ALTER TABLE ............................................................................................................. 28 4 INSERT INTO .................................................................................................................... 30 5 UPDATE ........................................................................................................................... 32
Image of page 2
3 Table of Contents Tutorial: Structured Query Language (SQL) 6 DELETE ............................................................................................................................. 34 7 SELECT ............................................................................................................................. 36 7.1 The ORDER BY Keyword ............................................................................................ 38 7.2 SELECT DISTINCT ....................................................................................................... 39 7.3 The WHERE Clause .................................................................................................... 39 7.3.1 Operators .......................................................................................................... 40 7.3.2 LIKE Operator .................................................................................................... 40 7.3.3 IN Operator ........................................................................................................ 41 7.3.4 BETWEEN Operator ........................................................................................... 41 7.4 Wildcards .................................................................................................................. 41 7.5 AND & OR Operators ................................................................................................ 42 7.6 SELECT TOP Clause .................................................................................................... 43 7.7 Alias .......................................................................................................................... 44 7.8 Joins .......................................................................................................................... 44 7.8.1 Different SQL JOINs ........................................................................................... 45 8 SQL Scripts ....................................................................................................................... 47 8.1 Using Comments ....................................................................................................... 47 8.1.1 Single-line comment .......................................................................................... 47 8.1.2 Multiple-line comment ...................................................................................... 47 8.2 Variables ................................................................................................................... 48 8.3 Built-in Global Variables ........................................................................................... 49 8.3.1 @@IDENTITY ..................................................................................................... 49 8.4 Flow Control ............................................................................................................. 50 8.4.1 IF – ELSE ............................................................................................................. 50 8.4.2 WHILE ................................................................................................................ 51 8.4.3 CASE ................................................................................................................... 52
Image of page 3
4 Table of Contents Tutorial: Structured Query Language (SQL) 8.4.4 CURSOR ............................................................................................................. 53 9 Views ............................................................................................................................... 55 9.1 Using the Graphical Designer ................................................................................... 56 10 Stored Procedures ........................................................................................................ 60 10.1 NOCOUNT ON/NOCOUNT OFF .............................................................................. 63 11 Functions ...................................................................................................................... 65 11.1 Built-in Functions .................................................................................................. 65 11.1.1 String Functions ............................................................................................. 65 11.1.2 Date and Time Functions ............................................................................... 66 11.1.3 Mathematics and Statistics Functions ........................................................... 66 11.1.4 AVG() .............................................................................................................. 67 11.1.5 COUNT() ......................................................................................................... 67 11.1.6 The GROUP BY Statement .............................................................................. 68 11.1.7 The HAVING Clause ........................................................................................ 69 11.2 User-defined Functions ......................................................................................... 70 12 Triggers ......................................................................................................................... 71 13 Communication from other Applications ..................................................................... 74 13.1 ODBC ..................................................................................................................... 74 13.2 Microsoft Excel ...................................................................................................... 75 14 References .................................................................................................................... 77
Image of page 4
5 1 Introduction to SQL SQL (Structured Query Language) is a database computer language designed for managing data in relational database management systems (RDBMS). SQL, is a standardized computer language that was originally developed by IBM for querying, altering and defining relational databases, using declarative statements. SQL is pronounced / ˌɛ s kju ː ˈɛ l/ (letter by letter) or / ˈ si ː kw ə l/ (as a word). What can SQL do? SQL can execute queries against a database SQL can retrieve data from a database SQL can insert records in a database SQL can update records in a database SQL can delete records from a database
Image of page 5
6 Introduction to SQL Tutorial: Structured Query Language (SQL) SQL can create new databases SQL can create new tables in a database SQL can create stored procedures in a database SQL can create views in a database SQL can set permissions on tables, procedures, and views Even if SQL is a standard, many of the database systems that exist today implement their own version of the SQL language. In this document we will use the Microsoft SQL Server as an example.
Image of page 6
Image of page 7

You've reached the end of your free preview.

Want to read all 78 pages?

  • Fall '09
  • ........., Query language

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture

Stuck? We have tutors online 24/7 who can help you get unstuck.
A+ icon
Ask Expert Tutors You can ask You can ask You can ask (will expire )
Answers in as fast as 15 minutes