Tag: sql injection web applications

Uncategorized

Division Operator in SQL

Database Concept

DBMS ER Model

DBMS Relational model

DBMS Normalization

DBMS Basic SQL

DBMS DML Command

DBMS DCL Command

Advanced SQL

The division operator is used when we have to evaluate queries which contain the keyword ALL.

Some instances where division operator is used are:

  1. Which person has account in all the banks of a particular city?
  2. Which students have taken all the courses required to graduate?

In above specified problem statements, the description after the keyword 'all' defines a set which contains some elements and the final result contains those units which satisfy these requirements.

Another way how you can identify the usage of division operator is by using the logical implication of if...then. In context of the above two examples, we can see that the queries mean that,

  1. If there is a bank in that particular city, that person must have an account in that bank.
  2. If there is a course in the list of courses required to be graduated, that person must have taken that course.

Do not worry if you are not clear with all this new things right away, we will try to expain as we move on with this tutorial.

We shall see the second example, mentioned above, in detail.

Table 1: Course_Taken â†’ It consists of the names of Students against the courses that they have taken.

Student_NameCourse
RobertDatabases
RobertProgramming Languages
DavidDatabases
DavidOperating Systems
HannahProgramming Languages
HannahMachine Learning
TomOperating Systems

Table 2: Course_Required â†’ It consists of the courses that one is required to take in order to graduate.

Course
Databases
Programming Languages

Using Division Operator

So now, let’s try to find out the correct SQL query for getting results for the first requirement, which is:

Unfortunately, there is no direct way by which we can express the division operator. Let’s walk through the steps, to write the query for the division operator.

1. Find all the students

Create a set of all students that have taken courses. This can be done easily using the following command.

CREATE TABLE AllStudents AS SELECT DISTINCT Student_Name FROM Course_Taken

This command will return the table AllStudents, as the resultset:

Student_name
Robert
David
Hannah
Tom

2. Find all the students and the courses required to graduate

Next, we will create a set of students and the courses they need to graduate. We can express this in the form of Cartesian Product of AllStudents and Course_Required using the following command.

CREATE table StudentsAndRequired AS 
SELECT AllStudents.Student_Name, Course_Required.Course
FROM AllStudents, Course_Required

Now the new resultset – table StudentsAndRequired will be:

Student_NameCourse
RobertDatabases
RobertProgramming Languages
DavidDatabases
DavidProgramming Languages
HannahDatabases
HannahProgramming Languages
TomDatabases
TomProgramming Languages

3. Find all the students and the required courses they have not taken

Here, we are taking our first step for finding the students who cannot graduate. The idea is to simply find the students who have not taken certain courses that are required for graduation and hence they wont be able to graduate. This is simply all those tuples/rows which are present in StudentsAndRequired and not present in Course_Taken.

CREATE  table StudentsAndNotTaken AS 
SELECT * FROM StudentsAndRequired WHERE NOT EXISTS 
(Select * FROM Course_Taken WHERE StudentsAndRequired.Student_Name = Course_Taken.Student_Name 
AND StudentsAndRequired.Course = Course_Taken.Course)

The table StudentsAndNotTaken comes out to be:

Student_NameCourse
DavidProgramming Languages
HannahDatabases
TomDatabases
TomProgramming Languages

4. Find all students who cannot graduate

All the students who are present in the table StudentsAndNotTaken are the ones who cannot graduate. Therefore, we can find the students who cannot graduate as,

CREATE table CannotGraduate AS SELECT DISTINCT Student_Name FROM StudentsAndNotTaken
Student_name
David
Hannah
Tom

5. Find all students who can graduate

The students who can graduate are simply those who are present in AllStudents but not in CannotGraduate. This can be done by the following query:

CREATE Table CanGraduate AS SELECT * FROM AllStudents 
WHERE NOT EXISTS 
(SELECT * FROM CannotGraduate WHERE 
    CannotGraduate.Student_name = AllStudents.Student_name)

The results will be as follows:

Student_name
Robert

Hence we just learned, how different steps can lead us to the final answer. Now let us see how to write all these 5 steps in one single query so that we do not have to create so many tables.

SELECT DISTINCT  x.Student_Name FROM Course_Taken AS x WHERE NOT 
EXISTS(SELECT * FROM Course_Required AS y WHERE NOT 
EXISTS(SELECT * FROM Course_Taken AS z 
    WHERE z.Student_name = x.Student_name 
    AND z.Course = y.Course ))
Student_name
Robert

This gives us the same result just like the 5 steps above.

< prev
Next >
Uncategorized

Using the WHERE SQL clause

Database Concept

DBMS ER Model

DBMS Relational model

DBMS Normalization

DBMS Basic SQL

DBMS DML Command

DBMS DCL Command

Advanced SQL

WHERE clause is used to specify/apply any condition while retrieving, updating or deleting data from a table. This clause is used mostly with SELECT, UPDATE and DELETEquery.

When we specify a condition using the WHERE clause then the query executes only for those records for which the condition specified by the WHERE clause is true.

Syntax for WHERE clause

Here is how you can use the WHERE clause with a DELETE statement, or any other statement,

DELETE FROM table_name WHERE [condition];

The WHERE clause is used at the end of any SQL query, to specify a condition for execution.

Time for an Example

Consider a table student,

s_idnameageaddress
101Adam15Chennai
102Alex18Delhi
103Abhi17Banglore
104Ankit22Mumbai

Now we will use the SELECT statement to display data of the table, based on a condition, which we will add to our SELECT query using WHERE clause.

Let’s write a simple SQL query to display the record for student with s_id as 101.

SELECT s_id, 
    name, 
    age, 
    address 
    FROM student WHERE s_id = 101;

Following will be the result of the above query.

s_idnameageaddress
101Adam15Noida

Applying condition on Text Fields

In the above example we have applied a condition to an integer value field, but what if we want to apply the condition on name field. In that case we must enclose the value in single quote ' '. Some databases even accept double quotes, but single quotes is accepted by all.

SELECT s_id, 
    name, 
    age, 
    address 
    FROM student WHERE name = 'Adam';

Following will be the result of the above query.

s_idnameageaddress
101Adam15Noida

Operators for WHERE clause condition

Following is a list of operators that can be used while specifying the WHERE clause condition.

OperatorDescription
=Equal to
!=Not Equal to
<Less than
>Greater than
<=Less than or Equal to
>=Greate than or Equal to
BETWEENBetween a specified range of values
LIKEThis is used to search for a pattern in value.
INIn a given set of values
< prev
Next >
Uncategorized

SELECT SQL Query

Database Concept

DBMS ER Model

DBMS Relational model

DBMS Normalization

DBMS Basic SQL

DBMS DML Command

DBMS DCL Command

Advanced SQL

SELECT query is used to retrieve data from a table. It is the most used SQL query. We can retrieve complete table data, or partial by specifying conditions using the WHERE clause.

Syntax of SELECT query

SELECT query is used to retieve records from a table. We can specify the names of the columns which we want in the resultset.

SELECT 
    column_name1, 
    column_name2, 
    column_name3, 
    ...
    column_nameN 
    FROM table_name;

Time for an Example

Consider the following student table,

s_idnameageaddress
101Adam15Chennai
102Alex18Delhi
103Abhi17Banglore
104Ankit22Mumbai
SELECT s_id, name, age FROM student;

The above query will fetch information of s_id, name and age columns of the student table and display them,

s_idnameage
101Adam15
102Alex18
103Abhi17
104Ankit22

As you can see the data from address column is absent, because we did not specif it in our SELECT query.

Select all records from a table

A special character asterisk * is used to address all the data(belonging to all columns) in a query. SELECT statement uses * character to retrieve all records from a table, for all the columns.

SELECT * FROM student;

The above query will show all the records of student table, that means it will show complete dataset of the table.

s_idnameageaddress
101Adam15Chennai
102Alex18Delhi
103Abhi17Banglore
104Ankit22Mumbai

Select a particular record based on a condition

We can use the WHERE clause to set a condition,

SELECT * FROM student WHERE name = 'Abhi';

The above query will return the following result,

103Abhi17Rohtak

Performing Simple Calculations using SELECT Query

Consider the following employee table.

eidnameagesalary
101Adam265000
102Ricky428000
103Abhi2510000
104Rohan225000

Here is our SELECT query,

SELECT eid, name, salary+3000  FROM employee;

The above command will display a new column in the result, with 3000 added into existing salaries of the employees.

eidnamesalary+3000
101Adam8000
102Ricky11000
103Abhi13000
104Rohan8000

So you can also perform simple mathematical operations on the data too using the SELECT query to fetch data from table.

< prev
Next >
Uncategorized

Commit, Rollback and Savepoint SQL commandscommit commands

Database Concept

DBMS ER Model

DBMS Relational model

DBMS Normalization

DBMS Basic SQL

DBMS DML Command

DBMS DCL Command

Advanced SQL

Transaction Control Language(TCL) commands are used to manage transactions in the database. These are used to manage the changes made to the data in a table by DML statements. It also allows statements to be grouped together into logical transactions.

COMMIT command

COMMIT command is used to permanently save any transaction into the database.

When we use any DML command like INSERT, UPDATE or DELETE, the changes made by these commands are not permanent, until the current session is closed, the changes made by these commands can be rolled back.

To avoid that, we use the COMMIT command to mark the changes as permanent.

Following is commit command’s syntax,

COMMIT;

ROLLBACK command

This command restores the database to last commited state. It is also used with SAVEPOINT command to jump to a savepoint in an ongoing transaction.

If we have used the UPDATE command to make some changes into the database, and realise that those changes were not required, then we can use the ROLLBACK command to rollback those changes, if they were not commited using the COMMIT command.

Following is rollback command’s syntax,

ROLLBACK TO savepoint_name;

SAVEPOINT command

SAVEPOINT command is used to temporarily save a transaction so that you can rollback to that point whenever required.

Following is savepoint command’s syntax,

SAVEPOINT savepoint_name;

In short, using this command we can name the different states of our data in any table and then rollback to that state using the ROLLBACK command whenever required.

Using Savepoint and Rollback

Following is the table class,

idname
1Abhi
2Adam
4Alex

Lets use some SQL queries on the above table and see the results.

INSERT INTO class VALUES(5, 'Rahul');

COMMIT;

UPDATE class SET name = 'Abhijit' WHERE id = '5';

SAVEPOINT A;

INSERT INTO class VALUES(6, 'Chris');

SAVEPOINT B;

INSERT INTO class VALUES(7, 'Bravo');

SAVEPOINT C;

SELECT * FROM class;

The resultant table will look like,

idname
1Abhi
2Adam
4Alex
5Abhijit
6Chris
7Bravo

Now let’s use the ROLLBACK command to roll back the state of data to the savepoint B.

ROLLBACK TO B;

SELECT * FROM class;

Now our class table will look like,

idname
1Abhi
2Adam
4Alex
5Abhijit
6Chris

Now let’s again use the ROLLBACK command to roll back the state of data to the savepoint A

ROLLBACK TO A;

SELECT * FROM class;

Now the table will look like,

idname
1Abhi
2Adam
4Alex
5Abhijit

So now you know how the commands COMMIT, ROLLBACK and SAVEPOINT works.

< prev
Next >

Uncategorized

Using DELETE SQL command

Database Concept

DBMS ER Model

DBMS Relational model

DBMS Normalization

DBMS Basic SQL

DBMS DML Command

DBMS DCL Command

Advanced SQL

When you ask any question in Studytonight’s Forum it gets saved into a table. And using the Delete option, you can even delete a question asked by you. How do you think that works? Yes, using the Delete DML command.

Let’s study about the syntax and the usage of the Delete command.

DELETE command

DELETE command is used to delete data from a table.

Following is its general syntax,

DELETE FROM table_name;

Let’s take a sample table student:

s_idnameage
101Adam15
102Alex18
103Abhi17

Delete all Records from a Table

DELETE FROM student;

The above command will delete all the records from the table student.

Delete a particular Record from a Table

In our student table if we want to delete a single record, we can use the WHERE clause to provide a condition in our DELETE statement.

DELETE FROM student WHERE s_id=103;

The above command will delete the record where s_id is 103 from the table student.

S_idS_Nameage
101Adam15
102Alex18

Isn’t DELETE same as TRUNCATE

TRUNCATE command is different from DELETE command. The delete command will delete all the rows from a table whereas truncate command not only deletes all the records stored in the table, but it also re-initializes the table(like a newly created table).

For eg: If you have a table with 10 rows and an auto_increment primary key, and if you use DELETE command to delete all the rows, it will delete all the rows, but will not re-initialize the primary key, hence if you will insert any row after using the DELETE command, the auto_increment primary key will start from 11. But in case of TRUNCATE command, primary key is re-initialized, and it will again start from 1.

< prev
Next >