T-SQL Tutorial

SQL Tutorial


SQL Tutorial course teaches you how to use basics of SQL language.
It is a sql tutorial for beginners.

1. Introduction to SQL

    First of all you need to create an sql table in a SQL Server database.
    Uses the command create table.

    CREATE TABLE tutorials
    (
    id int,
    name varchar(150),
    duration int,
    price int
    );

    CREATE TABLE tutorial_orders
    (
    order_id int,
    tutorial_id int,
    order_date date
    );

    Insert rows into a sql table.

    INSERT INTO tutorials(id, name, duration, price) VALUES (1, 'SQL tutorial', 2, 200);
    INSERT INTO tutorials(id, name, duration, price) VALUES (2, 'T-SQL tutorial', 5, 300);
    INSERT INTO tutorials(id, name, duration, price) VALUES (3, 'Learn ASP', 5, 400);
    INSERT INTO tutorials(id, name, duration, price) VALUES (4, 'PHP tutorial', 3, 200);
    INSERT INTO tutorials(id, name, duration, price) VALUES (5, 'Learn HTML', 2, 100);

    INSERT INTO tutorial_orders(order_id, tutorial_id, order_date) VALUES (1, 3, getdate());
    INSERT INTO tutorial_orders(order_id, tutorial_id, order_date) VALUES (2, 1, getdate());
    INSERT INTO tutorial_orders(order_id, tutorial_id, order_date) VALUES (3, 6, getdate());

    Select information from a table.

    select * from tutorials;
    select * from tutorials where id=3;
    select * from tutorials where duration=2;
    select * from tutorials where price>200;
    select * from tutorials where name like '%SQL%';

    Modify the value of the columns in a table.

    update tutorials set price=price+20;
    update tutorials set name='Learn Microsoft ASP' where id=3;
    update tutorials set duration=1 where duration=2;
    update tutorials set price=price+100 where price>200;
    update tutorials set duration=4 where name like '%SQL%';

    Delete rows from a table.

    delete from tutorials;
    delete from tutorials where id=3;
    delete from tutorials where duration=2;
    delete from tutorials where price>200;
    delete from tutorials where name like '%SQL%';


    2. Extract and Filter data

    DISTINCT – eliminate duplicate rows in a select. Return distinct values from one or multiple columns of a table.

    select distinct duration from tutorials;

    WHERE – is the search condition for the rows returned by the select statement.

    select * from tutorials where id=3;

    AND – compare two expressions and return true if both expressions are true.

    select * from tutorials where duration=2 and price=100;

    OR – compare two expressions and return true if at least one expression is true.

    select * from tutorials where price=700 or duration=5;

    BETWEEN – return rows if the value is within the range of comparisons.

    select * from tutorials where price between 250 and 700;

    LIKE – return rows if a character string matches a pattern.

    select * from tutorials where name like '%SQL%';
    select * from tutorials where name like 'Le%';
    select * from tutorials where name like '%SQL';

    IN – return rows if a value is equal to one value in a list or a subquery.

    select * from tutorials where duration in (4,5);


    3. Group data, Limit and Sorting rows

    GROUP BY - divides the query result into groups of rows. The GROUP BY is used when an aggregate function exists in the select statement.

    select price, count(price) from tutorials group by price;

    HAVING - specifies a search condition for a group or an aggregate.

    select price, count(price)
    from tutorials
    group by price
    having count(price)>1;

    TOP – is used to limit the number of rows in a select query.

    select TOP 1 * from tutorials;
    select top(2) * from tutorials order by price desc;
    select TOP 50 PERCENT * from tutorials;

    ORDER BY – is used to sort rows returned in an select statement.

    select * from tutorials order by price DESC, id ASC;


    3. Table Joins

    INNER JOIN - returns only the rows for which there is an match in both tables.

    select * from tutorials t
    inner join tutorial_orders o
    on t.id = o.tutorial_id ;

    LEFT JOIN - returns all rows from the left table, even if there are no matches with the right table.

    select * from tutorials t
    left join tutorial_orders o
    on t.id = o.tutorial_id ;

    RIGHT JOIN - returns all rows from the right table, even if there are no matches with the left table.

    select * from tutorial_orders o
    right join tutorials t
    on o.tutorial_id = t.id;

    SELF JOIN - is used to join a table to itself.

    select * from tutorials t1, tutorials t2 where t1.id=t2.id;


    4. Subquery

    IN – check if a value is equal to one value in a subquery.

    select * from tutorials t
    where t.id IN (select o.tutorial_id from tutorial_orders o);

    ANY – compare if any value is equal to one value in a subquery.

    select * from tutorials t
    where t.id = ANY (select o.tutorial_id from tutorial_orders o);

    ALL – compare if all values is equal to all values in a subquery.

    select * from tutorials t
    where t.id = ALL (select o.tutorial_id from tutorial_orders o);

    SOME – match at least one row in the subquery.

    select * from tutorials t
    where t.id = SOME (select o.tutorial_id from tutorial_orders o);

    EXISTS – return rows if a subquery contains any rows.

    select * from tutorials t
    where EXISTS (select * from tutorial_orders o where o.tutorial_id=t.id);

    NOT EXISTS – return rows if a subquery not contains any rows.

    select * from tutorials t
    where NOT EXISTS (select * from tutorial_orders o where o.tutorial_id=t.id);


    5. Aggregate functions

    AVG - returns the average value of an expression.

    select avg(price) from tutorials;
    select avg(price) from tutorials where duration=5;

    SUM - returns the sum of all values of an expression.

    select sum(price) from tutorials;
    select sum(price) from tutorials where duration=5;

    MAX - returns the max value of an expression.

    select max(price) from tutorials;
    select max(price) from tutorials where duration=2;

    MIN - returns the min value of an expression.

    select min(price) from tutorials;
    select min(price) from tutorials where duration=5;

    COUNT - returns the number of rows returned by a select statement.

    select count(*) from tutorials;
    select count(*) from tutorials where price > 200;
    select price, count(*)
    from tutorials
    group by price
    having count(*)>1;