29 Feb Basics Of SQL
Basics Of SQL | SQL Tutorial For Beginners In 2020
Author:
Dr. Geeta Mandar Mete (Ph.D., M.C.S, MBA(HR and IT). This Article Published in Developer IQ Asia’s #1 Technology Magazine.
What is SQL?
SQL is a standard language that is used to work with databases. SQL stands for Structured Query Language. It allows you to access the database and manipulate the records within the database. SQL is a non-procedural language. It means that it dictates what has to be done without specifying how it should be done
What Are The Types Of SQL Statements?
SQL is accepted as a standard language. It is divided into the following
1. Data Definition Language
2. Data Manipulation Language
3. Data Retrieval
4. Transaction Control Language
5. Data Control Language
Data Definition Language allows us to add or make changes to the structure of the tables. The commands include CREATE, ALTER, DROP, RENAME and TRUNCATE.
Data Manipulation Language allows us to insert new rows, make changes to the existing rows as well as delete some of the rows from the tables. The commands used in this case include the INSERT, UPDATE, DELETE and MERGE.
Data Retrieval allows us to use the SELECT statement to retrieve the records from the database based on some condition.
Transaction Control Language allows us to control the transactions. There are three commands used to control the transactions. These include COMMIT, ROLLBACK, and SAVEPOINT.
Data Control Language allows the user to access the database and make changes to the tables based on the rights and privileges assigned to the user.
The commands include GRANT and REVOKE. In this article, we take a look at the DDL commands and understand how to use them. TOAD has been used to demonstrate the commands. We will consider the following scenario to understand how these commands work.
A database is to be designed for a company. The company employees people who are referred to as engineers. The company has to maintain the following details of the engineer like his employee number, name, designation, qualification, street, city, pin, date of joining and salary. Every Engineer works on a set of projects. The company also maintains information like the project number, name, budget and location. Along with this the information about which employee works on which project, how much time is spent on the project is also maintained. The clients contract these projects. Therefore there is a need to maintain the information about the client like his number, name, address and phone number. Along with this, it is necessary to maintain the information like the date of the contract.
If we look at the above scenario we understand that we need to create the following tables
1. Engineer
2. Project
3. Project_Engineer
4. Clients
5. Clients_Projects
♦ The CREATE TABLE Command
Syntax:
In the Engineer table, we will maintain the information like employee number, name, designation, qualification, street, city, pin, date of joining and salary. So the command to create the table Engineer will be
We see that the table with the name GM_ENGINEER 2011 has been created. Here NUMBER and VARCHAR2 are the data types available in Oracle. There are other data types like CHAR, DATE, LONG, BINARY INTEGER, TIMESTAMP, RAW, ROWID, CLOB, BLOB AND BFILE available in Oracle.
In a similar fashion, we create tables for Project, Project_Engineer, Clients, and Clients_Projects by using the following commands.
Here we note that we have applied the foreign key constraints in the ENGINEER_PROJECT and CLIENT_PROJECT tables so that we are able to relate the project with the engineer and the client with the project.
♦ The ALTER TABLE Command
The ALTER table command is used to make changes to the existing structure of the table or to add some more columns to the existing table.
Syntax 1:
Syntax 2:
Syntax 3:
Syntax 4:
Syntax 5:
Syntax 6:
Suppose we want to add the Project Start Date to the table GM_Project2011, then we will use the alter table command to achieve that. The command would be
We can also use the ALTER TABLE command to add many columns at a go. Let’s say we want to add the client’s street information, the client’s email and client’s nature of the business, we can use the ALTER TABLE command in the following way
We can use the ALTER TABLE command to drop a column in the following way
Thus we get the following tables with the structures as shown in the below diagram
♦ The DROP Command
The DROP command will remove the table along with its structure from the database. It will remove the rows in the table along with the indexes and the privileges. This operation cannot be rolled back. Oracle database versions before 10g cannot recover the table back. Oracle 10g provides a command FLASHBACK which allows us to recover the table back.
Syntax:
♦ The RENAME Command
The RENAME command renames the object from old name to new name
Syntax:
This command will rename the table CLIENT_ PROJECT to the new name GM_CLIENT_PROJECT.
♦ The TRUNCATE Command
The TRUNCATE command also removes the table from the database but it preserves the structure of the table.
Syntax:
Thus the DDL commands help the user to define the structure of the tables and to modify them whenever required.
ORACLE APPS R12 TECHNICAL ONLINE/CLASSROOM TRAINING COURSE WITH 100% WRITTEN JOB GUARANTEE
Related Topics
SQL INTERVIEW QUESTIONS AND ANSWERS 2020
No Comments