SQL Notes First Class

 

Query ‘

CREATE TABLE employees

( employee_id INT PRIMARY KEY,

  last_name VARCHAR(50) NOT NULL,

  first_name VARCHAR(50) NOT NULL,

  );

 

Add primary key in existing coloum

ALTER TABLE student alter column roll int NOT NULL

 

 

ALTER TABLE student  ADD PRIMARY KEY (roll)

 

 

 

 

 

 

 

 

Alter  coloumn  change datatype

 

ALTER TABLE  tablename

ALTER COLUMN   coloumnname   NVARCHAR(MAX) NULL;

 

 

 

KEY  CONCEPT

CREATE FOREGIN KEY

 

CREATE TABLE products

( product_id INT PRIMARY KEY,

  product_name VARCHAR(50) NOT NULL,

  category VARCHAR(25)

);

 

CREATE TABLE inventory

( inventory_id INT PRIMARY KEY,

  product_id INT NOT NULL,

  quantity INT,

  min_level INT,

  max_level INT,

  CONSTRAINT fk_inv_product_id

    FOREIGN KEY (product_id)

    REFERENCES products (product_id)  //PRODUCTS  PARENTS TABLE 

);

 

 

 

 

 

 

 

Forigen key  alter table

ALTER TABLE inventory

ADD CONSTRAINT nrsolu

    FOREIGN KEY (product_id)

    REFERENCES products (product_id);

 

 

Create primary key

CREATE TABLE employees

( employee_id INT PRIMARY KEY,

  last_name VARCHAR(50) NOT NULL,

  first_name VARCHAR(50) NOT NULL,

  salary MONEY

);

 

Primary using  constraint

CREATE TABLE employees

( employee_id INT,

  last_name VARCHAR(50) NOT NULL,

  first_name VARCHAR(50) NOT NULL,

  salary MONEY,

  CONSTRAINT employees_pk PRIMARY KEY (employee_id)

);

 

 

 

Primary key alter command

ALTER TABLE employees

ADD CONSTRAINT employees_pk PRIMARY KEY (employee_id);

 

Unique key

 

CREATE TABLE employees

( employee_id INT PRIMARY KEY,

  employee_number INT NOT NULL,

  last_name VARCHAR(50) NOT NULL,

  first_name VARCHAR(50),

  salary MONEY,

  CONSTRAINT employees_unique UNIQUE (employee_number)

);

 

Unique using alter table

ALTER TABLE employees

ADD CONSTRAINT employees_unique UNIQUE (employee_number);

 

 

 

 

 

 

 

 

 

Check Constraints

This SQL Server tutorial explains how to use the check constraints in SQL Server (Transact-SQL) with syntax and examples.

Note

• A check constraint can NOT be defined on a SQL View.
• The check constraint defined on a table must refer to only columns in that table. It can not refer to columns in other tables.
• A check constraint can NOT include a Subquery.
• A check constraint can be defined in either a CREATE TABLE statement or a ALTER TABLE statement.

 

 

Create Check

CREATE TABLE nremployees1

( employee_id INT NOT NULL,

  last_name VARCHAR(50) NOT NULL,

  first_name VARCHAR(50),

  salary INT,

  CONSTRAINT NRCHECK

    CHECK (salary > 500)

);

 

Using  money datatype

CREATE TABLE employees

( employee_id INT NOT NULL,

  last_name VARCHAR(50) NOT NULL,

  first_name VARCHAR(50),

  salary MONEY,

  CONSTRAINT check_salary

    CHECK (salary > 0)

);

 

 

 

Alter check constraint

ALTER TABLE nrdata

ADD CONSTRAINT checksal CHECK (sal > 500);

 

Drop conatraint

 

ALTER TABLE nrdata

DROP CONSTRAINT checksal;

Disable a Check Constraint

 

ALTER TABLE nrdata

NOCHECK CONSTRAINT checksal;

Enable  a Check Constraint

 

ALTER TABLE nrdata

WITH CHECK CHECK CONSTRAINT checksal;

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

SQL Server Conditions

AND Condition	2 or more conditions to be met
OR Condition	Any one of the conditions are met
AND and OR	Combine AND and OR conditions
LIKE Condition	Use wildcards in a WHERE clause
IN Condition	Alternative to multiple OR conditions
NOT Condition	Negate a condition
IS NULL Condition	Test for a NULL value
IS NOT NULL Condition	Test for a NOT NULL value
BETWEEN Condition	Retrieve within a range (inclusive)
EXISTS Condition	Condition is met if subquery returns at least one row

 

LIKE Condition

This SQL Server tutorial explains how to use the LIKE condition in SQL Server (Transact-SQL) to perform pattern matching with syntax and examples.

Description

The SQL Server (Transact-SQL) LIKE condition allows wildcards to be used in the WHERE clause of a SELECT, INSERT, UPDATE, or DELETE statement. This allows you to perform pattern matching.

 

Wildcard	Explanation
%	Allows you to match any string of any length (including zero length)
_	Allows you to match on a single character
[ ]	Allows you to match on any character in the [ ] brackets (for example, [abc] would match on a, b, or c characters)
[^]	Allows you to match on any character not in the [^] brackets (for example, [^abc] would match on any character that is not a, b, or c characters)

 

SELECT *

FROM employees

WHERE last_name LIKE 'B%';

 

SELECT *

FROM employees

WHERE last_name LIKE '%o%';

 

SELECT *

FROM employees

WHERE first_name LIKE 'Ad_m';

This SQL Server LIKE condition example would return all employees whose first_name is 4 characters long, where the first two characters is 'Ad' and the last character is 'm'. For example, it could return employees whose first_name is 'Adam', 'Adem', 'Adim', 'Adom', 'Adum', etc.

 

 

 

SELECT *

FROM employees

WHERE employee_number LIKE '123_';

You might find that you are looking for an employee_number, but you only have 3 of the 4 digits. The example above, would retrieve potentially 10 records back (where the missing value could equal anything from 0 to 9). For example, it could return employees whose employee numbers are:

1230, 1231, 1232, 1233, 1234, 1235, 1236, 1237, 1238, 1239

 

 

SELECT INTO Statement

This SQL Server tutorial explains how to use the SELECT INTO statement in SQL Server (Transact-SQL) with syntax and examples.

Description

The SQL Server (Transact-SQL) SELECT INTO statement is used to create a table from an existing table by copying the existing table's columns.

It is important to note that when creating a table in this way, the new table will be populated with the records from the existing table (based on the SELECT Statement).

 

SELECT id,name

INTO nrdata1(new table )

FROM nrdata (old table )

WHERE sal>500;

 

 

 

 

DROP TABLE Statement

This SQL Server tutorial explains how to use the DROP TABLE statement in SQL Server (Transact-SQL) with syntax and examples.

Description

The SQL Server (Transact-SQL) DROP TABLE statement allows you to remove or delete a table from the SQL Server database.

 

DROP TABLE employees;