Create Syntax - SQL Fördjupning

CreateSyntax.sql

-- TIPS
-- Om du får 'jordgubbsland' utan förklarning kan det vara Intellisense cachen!
-- Intellisense i Query fönstret uppdaterar inte sig själv.
-- För att göra det manuellt gå till Edit -> Intellisense -> Refresh Local cache
-- Eller CTRL SHIFT R


-- CREATE DATABASE CREATE DATABASE CREATE DATABASE CREATE DATABASE CREATE DATABASE CREATE DATABASE
-- CREATE DATABASE CREATE DATABASE CREATE DATABASE CREATE DATABASE CREATE DATABASE CREATE DATABASE
-- CREATE DATABASE CREATE DATABASE CREATE DATABASE CREATE DATABASE CREATE DATABASE CREATE DATABASE
-- CREATE DATABASE CREATE DATABASE CREATE DATABASE CREATE DATABASE CREATE DATABASE CREATE DATABASE

-- Vi skapar en tabell tillsammans med hjälp av SSMS GUI först... klunkigt! Tidskrävande.
-- Stor risk för att göra fel.
-- Allt vi gör måste göras om... om databasen blir raderad eller korrupt.

-- Det finns ett bättre sätt! SYNTAXER!

-- Denna syntax kommer att skapa en helt ny databas på era lokala SQL Server
-- Vi klickar på antingen Execute eller F5
-- Se du den nya databasen?
CREATE DATABASE DeleteMe

-- ----------------------------------------------------------------------------------------------
-- Kan du gissa vad denna syntax gör? :)
DROP DATABASE DeleteMe

-- ----------------------------------------------------------------------------------------------
-- Om du är connected till dbas DeleteMe kommer inte DROP syntaxen att fungera!
-- Du får inte radera en databas som har active connections! (se dropdown listan i menyn högst upp)
-- För att lösa detta kan du connecta till en annan dbas temporärt
USE master
DROP DATABASE DeleteMe

-- ----------------------------------------------------------------------------------------------
-- Vi vill jobba med vår nya databas... så vi skapar den igen och se till att koppla upp oss mot den
-- `GO`-satsen är faktiskt inte en del av T-SQL-syntaxen; 
-- den är en batch-separatör som används av vissa verktyg, inklusive SQL Server Management Studio (SSMS). 
-- Den erkänns inte av SQL Server-databasmotorn i sig själv, utan används för att separera batches 
-- av T-SQL-kod vid sändning av förfrågningar till servern från verktyg som SSMS.

-- I SSMS, när du kör en skript som innehåller flera omgångar av T-SQL-kod, indikerar `GO`-satsen 
-- slutet på en batch och början på nästa. Varje omgång skickas sedan separat till SQL Server för utförande.

CREATE DATABASE DeleteMe
GO
USE DeleteMe

-- CREATE TABLE CREATE TABLE CREATE TABLE CREATE TABLE CREATE TABLE CREATE TABLE CREATE TABLE
-- CREATE TABLE CREATE TABLE CREATE TABLE CREATE TABLE CREATE TABLE CREATE TABLE CREATE TABLE
-- CREATE TABLE CREATE TABLE CREATE TABLE CREATE TABLE CREATE TABLE CREATE TABLE CREATE TABLE
-- CREATE TABLE CREATE TABLE CREATE TABLE CREATE TABLE CREATE TABLE CREATE TABLE CREATE TABLE

CREATE TABLE tbl_Employee
(
	FirstName VARCHAR(100),
	MiddleName VARCHAR(100),
	LastName VARCHAR(100),
	Age INT
)

-- ----------------------------------------------------------------------------------------------
-- Option 1
-- Om man inte inkluderar kolumnerna i INSERT raden så MÅSTE man ha VALUES för VARJE kolumn
INSERT INTO tbl_Employee
VALUES ('Homer', 'J', 'Simpson', 51)

-- Option 2
-- TIPS - Ta bort siffran 49... kör igen... vad händer?
INSERT INTO tbl_Employee
VALUES ('Marge', null, 'Simpson', 49)

-- Option 3
-- Här definierar vi att endast 3 kolumner är relvanta för oss i denna insert
-- OBS: Detta fungerar endast om kolumnerna som saknas (MiddleName) tillåter null
INSERT INTO tbl_Employee(FirstName, LastName, Age)
VALUES 
('Lisa', 'Simpson', 11),
('Bart', 'Simpson', 9)

-- ----------------------------------------------------------------------------------------------
-- Det saknas en del viktiga saker från vår tbl_Employee
-- Vi skapar om tabellen som numera innehåller PK och några constraints (not null)
-- Först raderar vi den gamla sämre tabellen
DROP TABLE tbl_Employee

-- Visa tabellen i GUI... titta på PK... Identity ifylld! Woohoo!
CREATE TABLE tbl_Employee
(
    Id INT IDENTITY(1,1) PRIMARY KEY,
	FirstName VARCHAR(100) NOT NULL,
	MiddleName VARCHAR(100),
	LastName VARCHAR(100) NOT NULL,
	Age INT NOT NULL
)

-- fr1. Varför fungerar inte denna? Id?
INSERT INTO tbl_Employee(Id, FirstName, MiddleName, LastName, Age)
VALUES 
('Homer', 'J', 'Simpson', 51),
('Marge', null, 'Simpson', 49),
('Lisa', null, 'Simpson', 11),
('Bart', null, 'Simpson', 9)

-- fr2. Denna fungerar inte heller. Varför? Efternamn?
INSERT INTO tbl_Employee(FirstName, MiddleName, LastName, Age)
VALUES 
('Homer', 'J', 'Simpson', 51),
('Marge', null, 'Simpson', 49),
('Lisa', null, 'Simpson', 11),
('Bart', 'Simpson', null, 9)

-- fr3. Denna fungerar inte heller. Varför? Ålder?
INSERT INTO tbl_Employee(FirstName, MiddleName, LastName, Age)
VALUES 
('Homer', 'J', 'Simpson', 51),
('Marge', null, 'Simpson', 49),
('Lisa', null, 'Simpson', 11),
('Bart', null, 'Simpson', 'Nine')

-- fr4. Lägg märke till att denna faktiskt fungerar!
-- SSMS är så smart att den kasta om strängen 9 till en INT!
INSERT INTO tbl_Employee(FirstName, MiddleName, LastName, Age)
VALUES 
('Homer', 'J', 'Simpson', 51),
('Marge', null, 'Simpson', 49),
('Lisa', null, 'Simpson', 11),
('Bart', null, 'Simpson', '9')

-- Success! Hmmm dubletter!
INSERT INTO tbl_Employee(FirstName, MiddleName, LastName, Age)
VALUES 
('Homer', 'J', 'Simpson', 51),
('Marge', null, 'Simpson', 49),
('Lisa', null, 'Simpson', 11),
('Bart', null, 'Simpson', 9)

-- Funkar också... (utan att definiera kolumnerna)
-- Men nu MÅSTE vi insert data i exakt samma ordning som tabellen!
INSERT INTO tbl_Employee
VALUES 
('Homer', 'J', 'Simpson', 51),
('Marge', null, 'Simpson', 49),
('Lisa', null, 'Simpson', 11),
('Bart', null, 'Simpson', 9)

-- UPDATE TABLE UPDATE TABLE UPDATE TABLE UPDATE TABLE UPDATE TABLE UPDATE TABLE UPDATE TABLE
-- UPDATE TABLE UPDATE TABLE UPDATE TABLE UPDATE TABLE UPDATE TABLE UPDATE TABLE UPDATE TABLE
-- UPDATE TABLE UPDATE TABLE UPDATE TABLE UPDATE TABLE UPDATE TABLE UPDATE TABLE UPDATE TABLE
-- UPDATE TABLE UPDATE TABLE UPDATE TABLE UPDATE TABLE UPDATE TABLE UPDATE TABLE UPDATE TABLE

-- Jag glömde lägga till telefon...
ALTER TABLE tbl_Employee ADD Telephone VARCHAR(30) NOT NULL

-- 'Telephone' kolumn tillåter INTE null...
-- Om det redan finns data i denna tabell kommer inte denna syntax att tillåtas!
-- Var skulle hända med alla poster som redan fanns!?
-- De kommer att bli tilldelade värdet null... trots att det egentligen inte tillåts...
-- Detta strider mot contraint och kommer därför att skapa en error!
-- Om det inte finns data i tabellen redan kommer syntaxen att fungera utan problem :)

-- Vi behöver inte Telephone kolumnen.. .vi raderar den.
ALTER TABLE tbl_Employee DROP COLUMN Telephone

-- GUI SKAPAR SYNTAX GUI SKAPAR SYNTAX GUI SKAPAR SYNTAX GUI SKAPAR SYNTAX GUI SKAPAR SYNTAX
-- GUI SKAPAR SYNTAX GUI SKAPAR SYNTAX GUI SKAPAR SYNTAX GUI SKAPAR SYNTAX GUI SKAPAR SYNTAX
-- GUI SKAPAR SYNTAX GUI SKAPAR SYNTAX GUI SKAPAR SYNTAX GUI SKAPAR SYNTAX GUI SKAPAR SYNTAX
-- GUI SKAPAR SYNTAX GUI SKAPAR SYNTAX GUI SKAPAR SYNTAX GUI SKAPAR SYNTAX GUI SKAPAR SYNTAX
-- TIPS
-- När man använder ADD behöver man INTE skriver COLUMN
-- När man använder DROP MÅSTE man skriva COLUMN
-- ----------------------------------------------------------------------------------------------
-- TIPS
-- Man kan få hjälp från SSMS GUI att skapa syntaxer också!
-- 1. Högerklick på 'tbl_Employee' i Obejct Explorer till höger
-- 2. Välj 'Script Table as'
-- 3. Välj 'Create To'
-- 4. Välj 'New Query Editor Window'

-- Till slut får man denna syntax...
-- Det finns en hel del i denna syntax som vi inte förstår (och inte behöver förstå!)
-- Men... vi ser exakt var och hur tabellen skapas.
-- Användbar om du vill skicka till en arbetskollega :)
USE [DeleteMe]
GO

/****** Object:  Table [dbo].[tbl_Employee]    Script Date: 2023-08-09 10:09:51 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[tbl_Employee](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[FirstName] [varchar](100) NOT NULL,
	[MiddleName] [varchar](100) NULL,
	[LastName] [varchar](100) NOT NULL,
	[Age] [int] NOT NULL,
PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

-- ----------------------------------------------------------------------------------------------
-- RELATIONS RELATIONS RELATIONS RELATIONS RELATIONS RELATIONS RELATIONS RELATIONS RELATIONS RELATIONS
-- RELATIONS RELATIONS RELATIONS RELATIONS RELATIONS RELATIONS RELATIONS RELATIONS RELATIONS RELATIONS
-- RELATIONS RELATIONS RELATIONS RELATIONS RELATIONS RELATIONS RELATIONS RELATIONS RELATIONS RELATIONS
-- RELATIONS RELATIONS RELATIONS RELATIONS RELATIONS RELATIONS RELATIONS RELATIONS RELATIONS RELATIONS

-- Homer jobbar på en Nuclear Power Plant...
-- Så vi skapar en ny tabell som heter 'Employer'
CREATE TABLE tbl_Employer
(
    Id INT IDENTITY(1,1) PRIMARY KEY,
	CompanyName VARCHAR(100) NOT NULL,
)

INSERT INTO tbl_Employer(CompanyName)
VALUES 
('Nuclear Power Plant')

-- Vi vill kunna skapa en tabell och relaterar Homer till rätt arbetsplats
-- Enklast är att lägga till en ny kolumn i Employee tabellen med en Foreign key
-- Lisa och Bart har inget jobb... så vi tillåter NULL
ALTER TABLE tbl_Employee ADD EmployerId INT

-- Nu kan vi skriva syntaxen som kopplar employer tabellen till employee
ALTER TABLE [dbo].[tbl_Employee]  WITH CHECK ADD  CONSTRAINT [FK_tbl_Employee_tbl_Employer] FOREIGN KEY([EmployerId])
REFERENCES [dbo].[tbl_Employer] ([Id])
GO

-- Nu kontrollerar vi att relationen finns... och inga felaktiga kopplingar får göras i framtiden
ALTER TABLE [dbo].[tbl_Employee] CHECK CONSTRAINT [FK_tbl_Employee_tbl_Employer]
GO

-- Och till slut... se vi till att Homer arbetar på rätt arbetsplats
-- Denna kommer INTE att fungera! Eftersom det finns inga EmployerIds med värdet 1001!
UPDATE tbl_Employee
SET EmployerId = 1001
WHERE FirstName = 'Homer';

-- Denna fungerar! :)  Eftersom det finns en EmployerId med värdet 1!
UPDATE tbl_Employee
SET EmployerId = 1
WHERE FirstName = 'Homer';

-- Nu kan vi ställa en fråga till vår databas (mer om detta i en senare kurs)
SELECT * FROM tbl_Employee ee
INNER JOIN tbl_Employer er ON er.Id = ee.Id

-- ----------------------------------------------------------------------------------------------