SQL Database Fundamentals
Structured Query language is one of the basic building blocks of the advanced database architecture. SQL defines the methods that are used to create and manipulate relational databases all the major platforms. It can be called as Sequel. It comes in many flavours. Oracle databases use their proprietary PL/SQL, Microsoft SQL server that makes to Transact SQL.
DDL and DML
SQL commands that will be categorised into two main sub languages. The Data Definition Language that contains the commands used to create and destroy databases and database objects. After the database structure which is defined with DDL database administrators and users will be used to insert, retrieve and manipulate or modify the data that will be contained. SQL also assists the Data control language. DCL will governs the security that access the objects within the Database.
Data definition language (DDL) commands:
The Data definition language is used to create and destroy databases and database objects. These commands are primarily used by database administrators during the setup and setup and removal phases of database project. DDL revolves around four primary commands-create, use, alter and Drop.
This create command will establishe the databases, tables or queries on your platform. For example the below command:
create database employees1;
This will create the database employees1. After we create another variant database, the next step is to create tables which contains data and accomplishes the task
Create Table personal info 11(first name char(20), last_name char(20,employee_id11 int );
-establishes a titled personal_info11 in current database.
The USE command will show the active database. For example, if we are presently working in the sales database and want to issue some commands which will affect the employee database, preface them with the below sql command:
Double check the database, if we are working modify its definition through the alter command which changes to structure of a table without deleting and recreating it. Take a look at the following command
ALTER TABLE personal_info 11 ADD salary1 money null;
This is an example that adds a new attribute to the ‘personal_info’ table employee’s salary. The money argument that says that an employee’s salary stores by using a dollars and cents format. The null keyword tells the database that is ok for the field that contain no value that is given employee salary.
The final command of the data definition language- DROP will remove the whole database objects from our DBMS. For example to permanently remove the personal_info table that we created use following command.
DROP TABLE personal_info11;
2. Data Manipulation Language(DML) commands
The Data Manipulation Language(DML) is used to retrieve, insert and modify database information. The DML commands offer the typical framework for interacting within the database on a routine basis.
The insert command will add records to an existing table. Returning to the personal info example from the previous section imagine that our HR department needs to add a new employee to its database.
INSERT INTO personal_info11
This select command is the commonly used command. It retrieves main information from any sql operational database. This command shown below retrieves all the information with the personal_info table like
Select * from personal_info11;
It limits the attributes which are retrieved from the database by knowing what will be selected. For example, the human resources department will require a list of the last names of all the employees in the company. Like
Here the where command will be having a limit the records that are retrieved to those that will be meet specified criteria.The CEO will be interested while in reviewing all the personnel records of all the highly paid employees like
WHERE salary1 > $50000;
The update command modifies the information will contain within the table by bulk or individually. Assume the company which gives all employees a 3 percent cost of living that increase in their salary annually.
SET salary1 = salary 1* 1.03;
- What is SQL?
- What are DDL commands?