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 usefor 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 .
- INSERT INTO
- ALIAS IN SQL
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.
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.
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.
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 .
DROP ObjectType ObjectName
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!