SQL Queries
In this article, we will be learning about create, Alter, insert, update and select queries.
SQL stands for Structured Query Language. Before knowing about SQL queries. we must know what is database?
What is Database?
Database is a repository where we can store the data. Many types of database is available. We are going to look at Relational Database. If the data is stored in tabular format, Then it is called Relational Database. Now, we will move on to SQL. SQL is a query language which helps to
- create database and table
- insert records in the table
- Alter the table
- update the records
- retrieve the data from the database
I have used Microsoft SQL Server Management studio to write queries. if you want to use MySQL, you can use.
Lets get started.
Lets first create our database.
Create database is the query to create database. here, studentt is the database name.
Next we are going to create table in the studentt database.
We are going to create students’ mark table. Here student_details is the table name. sid,name,tam,eng,mat,sci are the field name. We have to give field name along with its type.
student id(sid) field will have only integers which means only numbers. Student id cannot be alphanumeric or any other format. I have given constraint as not null primary key which means the field sid should not be null(cannot be left blank) and i have set sid field as primary key as it is going to uniquely identify the record.
Let’s understand about primary key by an example.
What is Primary Key?
Primary key helps to uniquely identify the records in the database table.
Consider, we have 1000 students’ record in Student table which has fields such as admission number, student_name, father’s name, address and mobile_no. Now we want to retrieve the details of a student whose name is john. In this 1000 students’ record, more than one student can have the name called ‘john’. So student_name field cannot be set as primary key. However, Admission number for each student will be unique. So admission number field can be set as primary key which will help to retrieve the data.
Now, go back to the above image. name field type is varchar(50) which means 50 characters can be entered in the name field. subject fields such as tamil(tam), english(eng),maths(mat),science(sci) will have marks which are obviously numbers. Hence i have set the type as integer.
Now we have created the required table, lets look at the structure of the table by using select query
Well, Our table has been created. Now i want to add one more subject field called soc(social). so, if we want to add column, we have to use alter command
Alter table table name add field_name datatype.
after executing this query, we have to use select query to see the structure of the table.
The new field soc is added.
Next we are going to insert records in the table.
insert query can be written in two ways.
insert into table_name values(values must be give in the order of the field)
here 1000 is the sid, David is the name , 80,90,75,45,60are the 5 subject marks. let’s retrieve the record using select query
Note: string must be given in quotes. eg: ‘David’
let’s insert one more record by using another way.
insert query can be written by giving the fields’ name in the bracket before the keyword ‘value’. [i have circled in the image]
let’s fetch the record from table by using select command.
i have used * in the select query which helps to show all the fields.
if i want to show only name field then i have to write query as
select name from student_details
we can replace the * by any field which we want to display. if we want to show more than one record, then the name of the fields must be given and must be separated by commas(,) let’s understand by seeing the below image.
now we move on to update command
Actual social mark for the student, richard is 96. By mistake it has be entered as 78. Now i want to update the soc field for the student whose name is richard.
update table name set field name(which field you want to update)= new value. Here, i have used where clause which is very important. If we do not use where clause the value will be updated to all the records.
consider, we have a table called employee which has 500 employees’ record in which phone_number is one of the fields. Now, an employee whose name is jane wants to change his phone no. If you write the update query without using where clause, jane’s new phone number will be updated to all the 500 employees.
So we must be very careful while updating the database table.
Writing queries is not case sensitive.
Hope you enjoyed reading. If you like this article, give a clap, follow me on Twitter and Github. If you want to clarify anything in SQL queries, Please do comment. Stay tuned…!!!
