SQL Server Management Studio & SQL DDL

1. SQL Server Management Studio & SQL DDL • SQL Server Management Studio • SQL DDL • CREATE TABLE • Constraints • ALTER TABLE • DROP TABLE • The GUI way • Steen Jensen, autumn 2013

2. SQL Server Management Studio A lot of the time you will be working in the Query window Most of the commands can be executed in a graphical way, but start training typing in the SQL commands The management studio is huge and a lot of functionality is aimed for administrators And now for a live demo!

3. SQL DDL • SQL stands for Structured Query Language and can be divided into two groups of commands: • DDL: Data Definition Language for handling the structure • DML: Data Manipulation Language for handling the content • In SQL Server Management Studio you will be using a dialect of SQL called T-SQL (Transact SQL), which is largely compliant with ANSI/ISO SQL standards • All queries work on objects – a fully qualified object name: • [ServerName.[DatabaseName.[SchemaName.]]]ObjectName • A schema can be used for separating database objects into logical groups – default schema = dbo(= database owner) • An object name must always be part of a query – the rest are optional

4. Creating tables – overall structure The command CREATE TABLE is used to create a table and have the below general structure:

5. CREATE TABLE – selected parts • Identity: • SQL Server automatically assigns a sequenced number for every inserted/new row (other DBMS’s calls this “auto_increment”) • Seed is the start value, and increment how much it should be increased • Computed columns: • Used for derived attributes/columns • Column constraints: • Covered in chapter 6 (e.g. Primary key)

6. CREATE TABLE – table & column names • Selected rules for table & column names: • Capitalization: start each word with a capital letter (“camel casing”) • Name length: keep names as short as possible • Limit abbreviations: only use well-known abbreviations (e.g. “no” ) • Eliminate spacing between words: use camel casing • Avoid underscore : “tricky” to type, difficult to read (underline)

7. CREATE TABLE – data types 1

8. CREATE TABLE – data types 2

9. CREATE TABLE – data types 3

10. CREATE TABLE – data types 4

11. Creating tables – example The below create command creates a table called Employees

12. Constraints – primary key Different constraints can be added for a table Primary key constraints:

13. Constraints – foreign key Foreign key constraints:

14. Constraints – foreign key, self-referencing Foreign key constraints – self-referencing:

15. Constraints – unique • Unique constraints: • Often referred as alternate keys

16. Constraints – check 1 • Check constraints: • Can make a lot of validation

17. Constraints – check 2 Below is a little subset of, what is possible using the check constraint

18. Constraints – default • Default constraints: • If a value is supplied in an insert, then no default value will be used • If no value is supplied in an insert, then the default value will be used

19. Exercise in CREATE TABLE Continue with your Amazon system and create the necessary tables in SQL Server Management Studio

20. Altering tables – overall structure The command ALTER TABLE is used to change an existing table and have the below general structure:

21. Altering tables – an example The below figure shows two examples of adding new fields/columns to an existing table:

22. Deleting tables The command DROP TABLE is used to delete the structure and the content of an existing table:

23. The GUI way of doing things Instead of typing in SQL commands you can do it in a graphical way Try this by following the instructions page 165bot – 170bot