Friday 12 June 2015

SQL Basics

What is SQL?

SQL stands for Structured Query Language
SQL lets you access and manipulate databases
SQL is an ANSI (American National Standards Institute) standard



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
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
------------------------------------------------------------------

RDBMS


RDBMS stands for Relational Database Management System.

RDBMS is the basis for SQL, and for all modern database systems such as MS SQL Server, IBM DB2, Oracle, MySQL, and Microsoft Access.

The data in RDBMS is stored in database objects called tables.

A table is a collection of related data entries and it consists of columns and rows.


Some of The Most Important SQL Commands




SELECT - extracts data from a database
UPDATE - updates data in a database
DELETE - deletes data from a database
INSERT INTO - inserts new data into a database
CREATE DATABASE - creates a new database
ALTER DATABASE - modifies a database
CREATE TABLE - creates a new table
ALTER TABLE - modifies a table
DROP TABLE - deletes a table
CREATE INDEX - creates an index (search key)
DROP INDEX - deletes an index



SQL SELECT Statement

The SELECT statement is used to select data from a database.

The result is stored in a result table, called the result-set.

SQL SELECT Syntax

SELECT column_name,column_name
FROM table_name;
and

SELECT * FROM table_name;

Eg: SELECT CustomerName,City FROM Customers;
Eg:SELECT * FROM Customers;


The SQL SELECT DISTINCT Statement


In a table, a column may contain many duplicate values; and sometimes you only want to list the different (distinct) values.

The DISTINCT keyword can be used to return only distinct (different) values.

SQL SELECT DISTINCT Syntax

SELECT DISTINCT column_name,column_name
FROM table_name;

Eg: SELECT DISTINCT City FROM Customers;

The SQL WHERE Clause

The WHERE clause is used to extract only those records that fulfill a specified criterion.

SQL WHERE Syntax

SELECT column_name,column_name
FROM table_name
WHERE column_name operator value;

Eg: SELECT * FROM Customers
WHERE CustomerID=1;

Operators in The WHERE Clause
The following operators can be used in the WHERE clause:

Operator    Description
=    Equal
<>    Not equal. Note: In some versions of SQL this operator may be written as !=
>    Greater than
<    Less than
>=    Greater than or equal
<=    Less than or equal
BETWEEN    Between an inclusive range
LIKE    Search for a pattern
IN    To specify multiple possible values for a column



The SQL AND & OR Operators


The AND operator displays a record if both the first condition AND the second condition are true.

The OR operator displays a record if either the first condition OR the second condition is true.

Eg: for AND operator
SELECT * FROM Customers
WHERE Country='Germany'
AND City='Berlin';

Eg: for OR operator
SELECT * FROM Customers
WHERE City='Berlin'
OR City='München';

Eg: for both AND and OR
SELECT * FROM Customers
WHERE Country='Germany'
AND (City='Berlin' OR City='München');



The SQL ORDER BY Keyword

The ORDER BY keyword is used to sort the result-set by one or more columns.

The ORDER BY keyword sorts the records in ascending order by default.
To sort the records in a descending order, you can use the DESC keyword.

SQL ORDER BY Syntax

SELECT column_name,column_name
FROM table_name
ORDER BY column_name,column_name ASC|DESC;

Eg: SELECT * FROM Customers
ORDER BY Country,CustomerName;

The SQL INSERT INTO Statement

The INSERT INTO statement is used to insert new records in a table.


SQL INSERT INTO Syntax

It is possible to write the INSERT INTO statement in two forms.

The first form does not specify the column names where the data will be inserted, only their values:

INSERT INTO table_name
VALUES (value1,value2,value3,...);
The second form specifies both the column names and the values to be inserted:

INSERT INTO table_name (column1,column2,column3,...)
VALUES (value1,value2,value3,...);

Eg: INSERT INTO Customers
VALUES ('Cardinal','Tom B. Erichsen','Skagen 21','Stavanger','4006','Norway');
or this SQL statement (including column names):

Eg :INSERT INTO Customers (CustomerName, ContactName, Address, City, PostalCode, Country)
VALUES ('Cardinal','Tom B. Erichsen','Skagen 21','Stavanger','4006','Norway');

Eg: INSERT INTO Customers (CustomerName, City, Country)
VALUES ('Cardinal', 'Stavanger', 'Norway');

**he CustomerID column is an AutoNumber field and is automatically updated with a unique number
for each record in the table.

AutoNumber is a type of data used in Microsoft Access tables to generate an automatically incremented
numeric counter. The default AutoNumber type has a start value of 1 and an increment of1.

The SQL UPDATE Statement


The UPDATE statement is used to update existing records in a table.

SQL UPDATE Syntax

UPDATE table_name
SET column1=value1,column2=value2,...
WHERE some_column=some_value;

Eg: UPDATE Customers
SET ContactName='Alfred Schmidt', City='Hamburg'
WHERE CustomerName='Alfreds Futterkiste';


The SQL DELETE Statement


The DELETE statement is used to delete rows in a table.

SQL DELETE Syntax

DELETE FROM table_name
WHERE some_column=some_value;

Eg: DELETE FROM Customers
WHERE CustomerName='Alfreds Futterkiste' AND ContactName='Maria Anders';

Delete All Data
It is possible to delete all rows in a table without deleting the table.
This means that the table structure, attributes, and indexes will be intact:

DELETE FROM table_name;

or

DELETE * FROM table_name;
------------------------------------------------------------------

The SQL SELECT TOP Clause
The SELECT TOP clause is used to specify the number of records to return.

The SELECT TOP clause can be very useful on large tables with thousands of records.
Returning a large number of records can impact on performance.

Note: Not all database systems support the SELECT TOP clause.

SQL Server / MS Access Syntax

SELECT TOP number|percent column_name(s)
FROM table_name;

SQL SELECT TOP Equivalent in MySQL and Oracle
MySQL Syntax

SELECT column_name(s)
FROM table_name
LIMIT number;
Example

SELECT *
FROM Persons
LIMIT 5;
Oracle Syntax

SELECT column_name(s)
FROM table_name
WHERE ROWNUM <= number;
Example

SELECT *
FROM Persons
WHERE ROWNUM <=5;

Eg: SELECT TOP 2 * FROM Customers;
Eg: SELECT TOP 50 PERCENT * FROM Customers;

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

The SQL LIKE Operator
The LIKE operator is used to search for a specified pattern in a column.
 The "%" sign is used to define wildcards (missing letters) both before and after the pattern.

SQL LIKE Syntax

SELECT column_name(s)
FROM table_name
WHERE column_name LIKE pattern;

Eg: SELECT * FROM Customers
WHERE City LIKE 's%';--------selects all customers with a City starting with the letter "s"

Eg : SELECT * FROM Customers
WHERE City LIKE '%s';--------selects all customers with a City ending with the letter "s"

Eg: SELECT * FROM Customers
WHERE Country LIKE '%land%';-------selects all customers with a Country containing the pattern "land"

Eg: SELECT * FROM Customers
WHERE Country NOT LIKE '%land%';---selects all customers with a Country NOT containing the pattern "land"

------------------------------------------------------------------
SQL Wildcards
SQL wildcards is used to substitute one or more characters when searching for data in a database.

Note: SQL wildcards must be used with the SQL LIKE operator!

With SQL, the following wildcards can be used:

%--A substitute for zero or more characters
_--A substitute for exactly one character
[charlist]----Any single character in charlist
[^charlist]
or

[!charlist]----Any single character NOT in charlist

Eg: SELECT * FROM Customers
WHERE City LIKE 'ber%';

Eg: SELECT * FROM Customers
WHERE City LIKE '%es%';

Eg: SELECT * FROM Customers
WHERE City LIKE '_erlin';---selects customer in the city that starts
 with any character, followed by "erlin" from the "Customers" table

Eg; SELECT * FROM Customers
WHERE City LIKE 'L_n_on';-----selects customers in the city that starts with a "L", followed by any character,
followed by "n", followed by any character, followed by "on", from the "Customers" table

Eg: SELECT * FROM Customers
WHERE City LIKE '[bsp]%';--------selects customers in cities starting with "b", "s", or "p" from the "Customers" table

Eg: SELECT * FROM Customers
WHERE City LIKE '[!bsp]%';------selects companies in cities NOT starting with "b" or "s" or "p" from the "Customers
------------------------------------------------------------------

he IN Operator
The IN operator allows you to specify multiple values in a WHERE clause.

SQL IN Syntax

SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1,value2,...)

47 comments:

  1. Very informative article. Its worth reading and i have bookmarked it. Thanks!
    Regards
    Software testing Company

    ReplyDelete

  2. this blog is really useful and it is very interesting thanks for sharing , it is really good and exclusive.

    salesforce Training in Chennai

    ReplyDelete
  3. A nice article here, i think that people who have grown up with the idea of using computers are showing more responsibility towards writing posts that are thoughtful, do not have grammar mistakes and pertinent to the post..

    Android Training in Chennai

    ReplyDelete
  4. Superb i really enjoyed very much with this article here. Really its a amazing article i had ever read. I hope it will help a lot for all. Thank you so much for this amazing posts and please keep update like this excellent article.

    Digital Marketing Company in Chennai

    ReplyDelete
  5. It was great post. This is going to be very useful content for us. Thanks for sharing with us.

    Data Science Institutes in Pune

    ReplyDelete
  6. This is a terrific article, and that I would really like additional info if you have got any. I’m fascinated with this subject and your post has been one among the simplest I actually have read.
    Data Science Training in Indira nagar
    Data Science Training in btm layout
    Python Training in Kalyan nagar
    Data Science training in Indira nagar
    Data Science Training in Marathahalli | Data Science training in Bangalore

    ReplyDelete
  7. After reading your post I understood that last week was with full of surprises and happiness for you. Congratz! Even though the website is work related, you can update small events in your life and share your happiness with us too.

    angularjs-Training in velachery

    angularjs Training in chennai

    angularjs-Training in pune

    angularjs-Training in chennai

    angularjs Training in chennai

    ReplyDelete
  8. This is really impressive post, I am inspired with your post, do post more blogs like this, I am waiting for your blogs.
    aviation courses in Bangalore
    aviation institute in Bangalore
    aviation courses
    airline courses

    ReplyDelete
  9. The knowledge of technology you have been sharing thorough this post is very much helpful to develop new idea.
    here by i also want to share this.
    Java training in Bangalore|best Java training in Bangalore
    Java training in Chennai
    Java training in Bangalore
    Java online training
    Java training in Pune

    ReplyDelete

  10. Inspiring writings and I greatly admired what you have to say , I hope you continue to provide new ideas for us all and greetings success always for you.
    Keep update more information..


    Selenium training in bangalore
    Selenium training in Chennai
    Selenium training in Bangalore
    Selenium training in Pune
    Selenium Online training
    Selenium interview questions and answers

    ReplyDelete
  11. This comment has been removed by the author.

    ReplyDelete
  12. This comment has been removed by the author.

    ReplyDelete
  13. The perception Pescador SOT Fishing kayak also feature 2-Level seat adjustment and breathable mesh. Seats can be lowered for comfortable and relaxed paddling or it can be raised for optimum level and control.

    Emotion Spitfire Sit-On-Top Kayak

    ReplyDelete
  14. Really very happy to say, your post is very interesting to read. I never stop myself to say something about it. You’re doing a great job. Keep it up…

    Looking for Hadoop Admin Training in Bangalore, learn from Softgen Infotech provide Hadoop Admin Training on online training and classroom training. Join today!

    ReplyDelete
  15. Als je een partytent huurt is het belangrijk om ook aan de aankleding en inrichting te denken. Het is wel zo praktisch om dat ook bij Colors Events te regelen. Wij kunnen behalve jouw partytent ook zorgen voor meubilair zoals statafels, al dan niet voorzien van rokken, stoelen of gewone tafels. Ook feestverlichting mag niet ontbreken als je een feest in de avonduren organiseert

    Bezoek: colorsevents.nl

    ReplyDelete
  16. Webactueel schakel je in als je een maatwerk WordPress website laten maken wilt. Door een maatwerk website te ontwikkelen is het mogelijk om meer leads te genereren. Daarbij hebben we altijd oren voor de wensen en eisen die jij als ondernemer hebt als het om jouw website gaat. Bovendien gaat het om meer dan alleen een website. Ook het toepassen van de juist online marketingstrategie helpt hier in grote mate bij. Wij kunnen dit allemaal voor je verzorgen.

    Bekijk website

    ReplyDelete
  17. Webactueel schakel je in als je een maatwerk WordPress website laten maken wilt. Door een maatwerk website te ontwikkelen is het mogelijk om meer leads te genereren. Daarbij hebben we altijd oren voor de wensen en eisen die jij als ondernemer hebt als het om jouw website gaat. Bovendien gaat het om meer dan alleen een website. Ook het toepassen van de juist online marketingstrategie helpt hier in grote mate bij. Wij kunnen dit allemaal voor je verzorgen.
    Lees meer

    ReplyDelete
  18. Zonder al te veel poespas gewoon een nieuwe, strakke en frisse wandafwerking? Dan is het spuiten van latex muurverf een goede uitkomst. En ook dan moet je bij Latex Spuiten XXL zijn. Het hanteren van de speciale spuiten voor latexverf hebben wij ons in de loop der jaren eigen gemaakt. Door de spuittechniek ontstaat er een egale structuur, die een stuk strakker is dan bij het sauswerk met een traditionele verfroller. Bovendien is de klus een stuk sneller geklaard – ondanks dat we de werkzaamheden minstens zo gepassioneerd als traditioneel binnenschilderwerk uitvoeren. Latex spuiten is daarom doorgaans voordeliger dan latexen met de verfroller. Disclaimer: dit is afhankelijk per situatie en oppervlakte.

    muurverf spuiten


    ReplyDelete
  19. 우리카지노 100%안전 검증된 카지노사이트 만 엄선하여 소개해드립니다 국내 업계1위 우리카지노계열 전통이 있는 온라인카지노 에서 안전하고 편안한 게임을 ..

    우리카지노

    ReplyDelete
  20. Really it was an awesome article about JAVA, very interesting to read.You have provided an nice article,Thanks for sharing.
    Java training in chennai | Java training in annanagar | Java training in omr | Java training in porur | Java training in tambaram | Java training in velachery

    ReplyDelete
  21. We are urgently in need of Organs Donors, Kidney donors,Female Eggs,Kidney donors Amount: $500.000.00 Dollars
    Female Eggs Amount: $500,000.00 Dollars
    WHATSAP: +91 91082 56518
    Email: : customercareunitplc@gmail.com
    Please share this post.

    ReplyDelete


  22. Excellent Blog! I would like to thank for the efforts you have made in writing this post.

    Data Scientist Course in pune

    ReplyDelete
  23. I have a mission that I’m just now working on, and I have been at the look out for such information ExcelR Data Science Courses

    ReplyDelete
  24. Get to know about your credit options with us quickly! Its so easy, 2 more steps, call us. Personal broker for end to end hassle free loan processing. Get it done today!

    mortgage sydney

    ReplyDelete
  25. Brookside CBD is the best CBD selling store in the Tennesseeb. It is first store that is owned and opearated by pharmacist
    Brookside CBD tennessee Wellness Store

    ReplyDelete
  26. We understand that selling your home can be a difficult and confusing process, especially if you are in behind in your payments or have a home in need of repair. Our We Buy Houses investors can simplify the process by making you a clear, cash offer to purchase your home, along with presenting other options that may be available to you.
    We Buy Houses Wauwatosa WI

    ReplyDelete
  27. pool cleaning service Get clean up your pool area with specialist clearing offerings business in an discount prices. Experienced understand how to fresh repair, matintenance and pool.

    best pool service near me

    ReplyDelete
  28. We understand that selling your home can be a difficult and confusing process, especially if you are in behind in your payments or have a home in need of repair. Our We Buy Houses investors can simplify the process by making you a clear, cash offer to purchase your home, along with presenting other options that may be available to you.

    We Buy Houses Menomonee Falls WI

    ReplyDelete
  29. We buy houses in Columbia, SC. Need to sell your house fast in Columbia, SC? Simple Home Exits buys houses in Columbia, SC for cash. Contact us today!
    cash home buyers

    ReplyDelete
  30. The Highest Grad And Best Quality Rated commercial Bounce Houses
    my review here

    ReplyDelete
  31. Bounce House Castle Inflatables and Party Event and Concessions Rentals. Reserve Online near South Milwaukee.
    you could check here

    ReplyDelete
  32. You guys will be greatful to know that our institution is conducting online CS executive classes and a free CSEET classes only for you guys. If anyone is interested then feel free to contact us or visit our website for more details https://uniqueacademyforcommerce.com/

    ReplyDelete
  33. Login Your IC Market Account To Read The Latest News About The Platform.

    ReplyDelete
  34. Set An Alert For USD TO INR FORECAST To Receive An Email When The Exchange Rate Changes. Alternatively, Bookmark The Page And Check Back Here Regularly.

    ReplyDelete
  35. There Are Many Complaints About XM REVIEW Broker In The Internet But You Should Read This Review Before Investing Your Money With Them. We Have Personally Tested XM Fx And Found It To Be A Scam, Avoid Them At All Costs!

    ReplyDelete
  36. Great post. keep sharing such a worthy information.
    Jewellery Billing Software
    Jewellery Billing Software

    ReplyDelete