SQL Database Fundamentals

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. 

Create

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.

Use

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:

Use employees

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.

DROP

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.

Insert command

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
values(‘bart’,’simpson’,12345,$45000);

Select

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

SELECT last_name
FROM personal_info;

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 

SELECT *
FROM personal_info11
WHERE salary1 > $50000;

Update

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.

UPDATE personal_info11
SET salary1 = salary 1* 1.03;

Questions

1. What is SQL?
2. What are DDL commands?