Introduction to Database Concepts

DBMS and SQL

A Database Management System (DBMS), is a software program that enables the users to access database, manipulate data and represent it. SQL (Structured Query Language) is one of the RDBMS examples. SQL divides its commands on the basis of functionalities performed by them

The main types of commands in SQL are:

1. DDL (Data Definition Language) – DDL manages table and index structure. The most basic commands of DDL are create, alter, rename and drop statements.
   • Create – create an object in the database.
     Syntax:-
CREATE TABLE table_name (
column1 datatype,
column2 datatype,
column3 datatype,
....
);

   • Drop – delete an object (table) in the database.
     Syntax:-
     DROP TABLE table_name;
   • Alter – modify the structure of an existing object in the database.
     Syntax:-
ALTER TABLE table_name
ADD column_name datatype;

2. DML (Data Manipulation Language) – DML is a subset of SQL used to add, update, and delete the data. They are the queries that modify data. The most basic commands of DML are Insert, Select, Update, Delete.
   • INSERT –
     Insert statement is used to insert data into an existing table. The Insert
     statement has the following format
     Syntax:-
INSERT INTO table_name (column1, column2, column3,...)
VALUES (value1, value2, value3, ...);

   • SELECT –
     The SELECT statement is used for retrieving the data stored in a single table or in multiple related tables. Select has three main keywords: SELECT, FROM and WHERE. The SELECT list is the list of columns (from one or multiple tables) or computed values that are to be retrieved from the query. The FROM statement specifies the table, tables, or views from which you want to fetch the data. The WHERE clause specifies a condition to include or exclude rows for the query’s output.
     Syntax:-
SELECT column1, column2,
FROM table_name;

     Here, column1 and column2 are the field names of the table from where you want to select data. If you want to select all the fields which are available in the table, use the following syntax:
SELECT * FROM table_name;

   • UPDATE –
     The update statement is used to modify the table data.
     Syntax:-
UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

   • DELETE –
     The delete statement is used to remove records permanently from a table.
     Syntax:-
DELETE FROM table_name WHERE condition;

   • TRUNCATE –
     The truncate statement removes all records from a table. It executes faster than a DELETE statement without a WHERE clause. TRUNCATE statement retains the structure of the table. It will also reinitialize the identity columns to the specified initial value. It looks like this:
TRUNCATE TABLE table_name;


Constraints In SQL-

Constraints are the conditions used to limit or control the type or values of data that the user can enter into the tables. The main categories of constraints are:-

1. Primary key constraints.
2. Foreign key constraints.
3. Default constraints.
4. Not null constraints.
5. Check constraints.
6. Unique constraints.

• Primary key constraints:-
  A Primary key constraint is a column or a set of columns that uniquely identify a row in the table. Although you can assign more than one field as the primary key, each table can have only one primary key. A primary key column value must be unique and not null.
• Foreign key constraints:-
  A foreign key constraint consists of a column or a set of columns that is related to column/s of a primary key table. It can be one to one or one to many relationships. The primary key is on one side of the relationship, whereas the foreign key is on the many sides of the relationship. It is possible to have multiple foreign keys in a table. Each foreign key relates to a primary key in another table. While adding records in a table with a foreign key, the value must be present in the related table with a primary key.
• Default constraints:-
  A default constraint is a default value that SQL Server automatically places in a particular field in a table. A default value can be constant, NULL, or a function. All fields except identity and time stamp fields can contain default values. Each column can have one default constraint.
• Not Null Constraints:-
  In certain situations, data in a field is required for example a father’s name is compulsory. The Not Null constraint enables you to accomplish this task.
• Check Constraints:-
  Check constraints can be used to limit the range of values that a user can enter into a column. Multiple Check constraints are allowed for a particular column.
• Unique Constraints:-
  A unique constraint requires that each entry in a particular column is unique. A unique constraint is created by creating a unique index. The UNIQUE constraint identifies each record in a database table. The difference between UNIQUE constraint and PRIMARY Key is that unique columns can contain null whereas the primary key column is not null. There can be only one primary key in a table but multiple unique keys are allowed in the table.

PHP programming is a valuable skill that a programmer can have. Let StudySection help you out with proving your programming skills through its PHP certification. StudySection provides beginner as well as expert-level certifications in PHP programming to prove your level of skills in PHP programming.

Share this...

Facebook

Twitter

Linkedin