INTRODUCTIONTO DATABASE
Database refers to a collection of electronic records that could be processed to produce
useful information. The data can be accessed, modified, managed, controlled and
organized to perform various data-processing operations. The data is typically indexed
across rows, columns and tables that make workload processing and data querying
efficient. There are different types of databases: Object- oriented, Relational,
Distributed, Hierarchical, Network and others.
A System to maintain, secure, manage and process the data stored in databases is known
as Database Management System.
SQL
• SQL is a standard language for accessing and manipulating databases.
• SQL stands for Structured Query Language.
• It is case insensitive.
• SQL can execute queries against a database, retrieve data from a database, insert
records to a database, update and delete records in a database.
• RDBMS stands for Relational Database Management System. The data is stored in
the form of tables.
• Rows in a table are called as Records and columns are called as Field in a database.
DATATYPES
An SQL data type refers to the type of data which can be stored in a column of a database
table.
DATATYPE USE
CHAR(SIZE) A fixed length string which can have letters, numbers, and special
characters. The size parameter specifies the column length in
characters which can vary from 0 to 255. Default size is 1.
VARCHAR(SIZE) A variable length string which can contain letters, numbers, and
special characters. The size parameter specifies the maximum string
length in characters which can vary from 0 to 65535.
INT A normal-sized integer that can be signed or unsigned. If signed, the
allowable range is from -2147483648 to 2147483647. If unsigned, the
allowable range is from 0 to 4294967295.
FLOAT Represents a floating point number.
DATE It is used for dates in ‘YYYY-MM-DD’ format.
PRIMARY KEY
A primary key is a special relational database table column (or combination of
columns) designated to uniquely identify all table records.
A primary key's main features are:
• It must contain a unique value for each row of data.
• It cannot contain null values.
CONSTRAINTS
Constraints are certain types of restrictions on the data values that an attribute can have. They
are used to ensure the accuracy and reliability of the data. It is not mandatory to define
constraint for each attribute of a table. Commonly used constraints are as follows:
Constraint Description
NOT NULL Ensures that a column cannot have NULL
values.
UNIQUE Ensure that all the values in a column are
unique.
DEFAULT A default value specified for the column if
no value is provided.
PRIMARY KEY The column which can uniquely identify a
column or a row.
CREATING TABLE
• The CREATE TABLE statement is used to create a new table in a database.
• Syntax:
CREATE TABLE table_name (
column1 datatype,
column2 datatype,
column3 datatype,
....
);
• The column parameters specify the names of the columns of the table.
• The datatype parameter specifies the type of data the column can hold (e.g. varchar,
integer, date, etc.).
• Example:
CREATE TABLE Persons (
PersonID int,
LastName varchar(255),
FirstName varchar(255),
Address varchar(255),
City varchar(255)
);
INSERT DATA IN A TABLE
• The INSERT INTO statement is used to insert new records in a table.
• Syntax:
INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);
• Example:
INSERT INTO Persons(PersonID,LastName,FirstName,Address,City)
VALUES(‘P12’,’Matti’,’William’,’14th Ave’,’Seattle’);
UPDATE DATA IN A TABLE
• The UPDATE statement is used to modify the existing records in a table.
• Syntax:
UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;
• Example:
UPDATE Customers
SET ContactName = 'Alfred Schmidt', City= 'Frankfurt'
WHERE CustomerID = 1;
DELETE DATA FROM A TABLE
• The DELETE statement is used to delete existing records in a table.
• Syntax:
DELETE FROM table_name WHERE condition;
• Example:
DELETE FROM Customers WHERE CustomerName='Alfreds Futterkiste';
EXTRACT DATA FROM A TABLE
• The SELECT statement is used to select data from a database.
• Syntax:
SELECT column1, column2, ...
FROM table_name;
• Example:
SELECT CustomerName,City FROM Customers;
• If you want to extract the entire data from a table use *
SELECT * FROM Customers;
DELETING A TABLE
• DROP statement is used to delete a table or a database.
• Syntax:
DROP table table_name;
• You can use DROP statement to remove a database also. But if you use it remove a
database, all the tables created within that database will also get deleted.
EXERCISE
1. Match the following:
Update Insert values in a table
Delete Restrictions in columns
Create Update existing information on a table
Constraints Create a table
Insert Delete an existing row from a table.
2. Fill in the blanks:
a. SQL stands for _______________
b. _________ statement is used to delete the table from a database.
c. ___________ symbol is used to extract all the contents from a table.
d. ___________ constraint ensures that all values in a row/column are
unique.
e. The datatype that specifies the floating-point numbers are ________
3. Create a student table with the columns Rollno, SName, SDateofbirth,
Grade, Mode_of_Transport.
4. Write the SQL query to select all the data from the Student table.
5. Analyse the table given below and answer the following questions:
EmpId Emp_Name Emp_Dept Emp_Shift
E005 Steve Sales Morning
E765 Micheal IT Night
E324 Dane HR Morning
E564 Jack IT Morning
a. Write the query to display the employee names who are working in
the Morning shift.
b. Write the query to update the department of the employee whose
Employee Id is E324 to sales