SQL Queries Practice & SQL Commands

SQL Queries Practice is important for anyone who wants to improve their SQL writing skills. We humans learn from examples and by actually practising what we read. So this is what we will do today.
We discussed how SQL Select query works and what is a SQL query  & the difference between SQL query & SQL Statement.  Before that we learned how to install MS SQL Server Management Studio Step by Step  in a very brief article.

This was all very important before we learn basic SQL queries . In this article , you will find SQL queries for practice with Syntax and examples because what good the article is if it doesn’t have examples for  what it’s for. Following are the Basic SQL Queries & Commands for practice today. You can use  SQL Fiddle  for sql queries practice , if you have not installed SQL Server Management Studio.

SQL Queries Practice-Prepare the data

Examples and queries that we will see here will run on our University Management System database . If you want to use these SQL queries for practice , create the same database , you need to just copy paste the script from here .

SQL COMMANDS

1. CREATE
2. INSERT INTO
3. SELECT
   • ALIAS IN SQL
4. UPDATE
5. ALTER
6. DROP

CREATE SYNTAX & EXAMPLE

SQL create is the command which is use to create database objects . We can create a  SQL relational database , table  , schema , views , functions and stored procedures using this command. We will see the Syntax for Create database & table only as we have a understanding of these two database objects. For the rest we will do a hands-on when we discuss them.
Let’s create a SQL relational database , we have a normalized Entity-Relation diagram for a Student management System. We will use that diagram and create an actual database for you to practice it on.

CREATE DATABASE SYNTAX

CREATE DATABASE [database_name]

CREATE & DATABASE are the keywords and database_name will be the name of your database.

EXAMPLE

CREATE DATABASE School;

CREATE TABLE SYNTAX

CREATE TABLE [schema_name].[table_name](
[column_name] [datatype] nullable [constraint if any],
[column_name] [datatype] nullable [constraint if any]
);

CREATE & TABLE two keywords then you will put your schema_name with the name of your table.  Start by the name of SQL column then it’s SQL datatype and at the end you have to specify whether this column should accept and at last the constraint on the column if any. Last column shouldn’t have comma at the end.

EXAMPLE

CREATE TABLE [dbo].[Student](
[Student_ID] [int] NOT NULL PRIMARY KEY ,
[Name] [varchar](50) NULL,
[Address_ID] int NULL,
[Gender] [nchar](10) NULL,
[DateOfBirth] [date] NULL,
[PhoneNumber] [int] NULL,
[Department_ID] [int] NULL
);

Primary_key is the constraint that we defined on our key column. We will see about constraints and how they are created in our coming articles. Therefore , if you want to learn subscribe us and keep visiting us for the updates.

INSERT INTO COMMAND :

This is the command that we use to insert data in our tables.

SQL INSERT INTO SYNTAX

INSERT INTO [schemaname].[tablename] (columnname)
VALUES (columnname);

INSERT INTO & VALUES these three keywords and schemaname is optional . However , best practices is to always mention it. The order in which you write colum name in INTO clause , should be followed by the same order for column names in VALUES clause.
You can insert data directly without mentioning the column names in INTO clause . The MS SQL management studio will take the data from VALUES clause and insert the data in order the columns are defined.  See Example to clear this up.

SQL INSERT INTO EXAMPLE

INSERT INTO [dbo].[Student]
([Student_ID]
,[Name]
,[Address_ID]
,[Gender]
,[DateOfBirth]
,[PhoneNumber]
,[Department_ID])
VALUES
(01
,'John'
, 01
,'Male'
,'1992-06-20'
,558585
,01
);

The order in which you will write the column names in both INTO & VALUES clause is very important.

SELECT  SYNTAX & EXAMPLE

You have created a database , table and also inserted the data into it. We have discussed SQL SELECT in previous article.

SQL SELECT SYNTAX

SELECT columnname1,columnname2;
FROM schemaname.tablename;

SQL SELECT EXAMPLE

SELECT [Student_ID]
,[Name]
,[Address_ID]
,[Gender]
,[DateOfBirth]
,[PhoneNumber]
,[Department_ID]
FROM [StudentManagementSystem].[dbo].[Student]

Result of executed query above will be like this

 ALIAS IN SQL

Student_ID can be named as student roll number , If it’s a foreign key in any other table , we can call it Student_FK . A column can be named for the sake of making it more readable.  The above query can also have alias for it’s columns. See below

SELECT [Student_ID] AS [Student Roll Number]
,[Name] AS [Student Name]
,[Address_ID]
,[Gender]
,[DateOfBirth]
,[PhoneNumber]
,[Department_ID]
FROM [StudentManagementSystem].[dbo].[Student]

Can you give alias to other columns? Practice this on your machine for a good hands-on.

UPDATE SYNTAX & EXAMPLE

If you have to update a row . You have to use UPDATE command.

SQL UPDATE SYNTAX

UPDATE schemaname.tablename
SET columnname(s)=newvalue
WHERE condition

UPDATE , SET & WHERE three keywords , then column name(s) which you want to update based upon the condition you mention in WHERE.

EXAMPLE

UPDATE [dbo].[Student]
SET [Student_ID] = 2
WHERE [Student_ID]=1

Let’s see what happens to Students table when we successfully execute this statement.

The Student_ID now has value 2 instead of 1. This is update command.

DROP SYNTAX & EXAMPLE

This command removes the existing database objects .

SYNTAX

DROP ObjectType ObjectName

EXAMPLE

DROP TABLE STUDENT
DROP DATABASE StudentManagementSystem

These are the very basic SQL Queries & Commands and they are also the very fundamental part of our database operations called. CRUD operations.

A database can perform Create , Read , Update and Delete operations known as CRUD operations

CRUD operations and these queries will stay with you from the basic query to advance level queries . Therefore , its very essential to have a good practice . Learn how to form the logic in terms of your schema and write the queries .
Few terms such as database objects , schema , view, functions & stored procedure which we have used in this post are explained in their own separate articles.
 
Share this with your friends & fellows  the article & movie of course. See you in the next post.
May The Force be with you!
Happy Learning!
Cheers!