SQL For Beginners : SQL Clauses

Maria Elena Morillo Tejada
May 16, 2022
3 min read

Updated: Jul 6, 2022

SQL is a standard used to query, update, delete and insert data into a table.

Clauses:

Select – is a clause used to show the data storage into a table.

		SELECT City FROM [dbo].[WorldCities];

Result of a query using 'SELECT' clause.

Delete – is used to eliminate information in a table. The Delete clause leave the register of the rows deleted in the file of a database.

       DELETE FROM [dbo].[WorldCities];
       or DELETE * FROM [dbo].[WorldCities];

Result of a query using 'DELETE' clause.

Update – is used to update a row in a table.

UPDATE [dbo].[WorldCities] SET COUNTRY = 'DOMINICAN REPUBLIC' WHERE COUNTRY ='Dominican Republic'

- set: is used to update a current value for another value.

Result of a query using 'UPDATE' Clause.

Insert into – is a clause used to add data in a table.

INSERT INTO [dbo].[WorldCitieS]([city], [city_ascii], [lat], [lng], [country], [iso2],[iso3], [admin_name], [capital], [population], [id])

VALUES('TEST', 'TEST', 55.23, 55.33,'TEST COUNTRY', 'TEST', 'TEST', 'TEST', 'TEST', 55555.33, 55.00)

GO

SELECT * FROM [dbo].[WorldCities] WHERE CITY = 'TEST'

In an Insert statement you have to specify all the columns that has the table and the new values for each column to be inserted.

Result of a query using 'INSERT INTO' Clause.

Distinct – is a clause used for retrieve all the different values within a table.

       SELECT DISTINCT Country FROM [dbo].[WorldCities] ORDER BY Country

In these image on the top you can see the difference between you use a DISTINCT clause and not.

Top – is a clause used to retrieve any specified quantity of rows in a select query.

     SELECT TOP 10 Country FROM [dbo].[WorldCities]
	 SELECT TOP 5  Country FROM [dbo].[WorldCities]

Truncate – is a clause used to delete all the values within a table. Is a kind of delete but the difference is that when the Truncate operation doesn’t leave a register in the log file. Also, truncate reinitialize the sequence in a table if it has the sequence set.

      TRUNCATE TABLE [dbo].[WorldCities]

Result of a query using 'TRUNCATE' Clause.

Where – is a clause used to specified which register will be change or showed.

      SELECT top 1 Country FROM [dbo].[WorldCities] WHERE Country = 'Uruguay'

Group by – is a clause that group all the values depending the column or columns chose.

    SELECT Country, COUNT(CITY) CityQuantity FROM [dbo].[WorldCities]      
    GROUP BY Country

Result of a query using 'GROUP BY' Clause.

Order by – is a clause that order the retrieve data by a column and in a establish order.

SELECT Country, COUNT(CITY) CityQuantity FROM [dbo].[WorldCities] GROUP BY Country ORDER BY Country

Result of a query using 'ORDER BY' Clause.

SQL server set an ASC order by default, if not you have to put the order type in the query.

Desc: to retrieve all the data in Descending order or Asc (Value by default) to retrieve the data in an Ascending Order.

SELECT Country,COUNT(CITY) CityQuantity FROM [dbo].[WorldCities] GROUP BY Country ORDER BY Country Desc

Result of a query using 'ORDER BY DESC' Clause.

Having – is a clause used like a where but is exclusive if the query has a grouping function.

SELECT Country, COUNT(CITY) CityQuantity FROM [dbo].[WorldCities]  GROUP BY Country 
HAVING COUNT(CITY)>500
ORDER BY Country DESC

This query shows you all the Countries that have more than 500 cities.

Result of a query using 'HAVING' Clause.

Insert into select – is a clause used to add data the data retrieve from a Select statement in a table.

**This clause is use if the table is created previously**

             INSERT INTO [dbo].[WorldCities_2]
             SELECT * FROM [dbo].[WorldCities]

Result of a query using 'INSERT INTO SELECT' Clause.

Select into – is a clause to select the values of a table and the insert this in another table, that is created during the execution of the statement.

**This clause creates the new table with the structure of the table specific in the FROM clause. **

SELECT * INTO [dbo].[WorldCities_5] FROM [dbo].[WorldCities]

Result of a query using 'SELECT * INTO' Clause.

Set of data used:

SQL For Beginners : SQL Clauses

Recent Posts

Comments