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
CREATE
INSERT INTO
SELECT
ALIAS IN SQL
UPDATE
ALTER
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.
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.
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!
This website uses cookies to improve your experience. We'll assume you're ok with this, but you can opt-out if you wish. Cookie settingsACCEPT
Privacy & Cookies Policy
Privacy Overview
This website uses cookies to improve your experience while you navigate through the website. Out of these cookies, the cookies that are categorized as necessary are stored on your browser as they are essential for the working of basic functionalities of the website. We also use third-party cookies that help us analyze and understand how you use this website. These cookies will be stored in your browser only with your consent. You also have the option to opt-out of these cookies. But opting out of some of these cookies may have an effect on your browsing experience.
Necessary cookies are absolutely essential for the website to function properly. This category only includes cookies that ensures basic functionalities and security features of the website. These cookies do not store any personal information.
Any cookies that may not be particularly necessary for the website to function and is used specifically to collect user personal data via analytics, ads, other embedded contents are termed as non-necessary cookies. It is mandatory to procure user consent prior to running these cookies on your website.
6 Replies to “SQL Queries Practice & SQL Commands”