DBMS & SQL Commands
DataBase: A database is an organized collection of interrelated data.
Database Management System: A database management system is a
software package with computer programs that controls the creation, maintenance,
and use of a database.
A DBMS allows different user application programs to concurrently access the same
database.
Well known DBMSs include Oracle, IBM DB2, Microsoft SQL Server,
Microsoft Access, PostgreSQL, MySQL, FoxPro, and SQLite.
Data can be organized into two types:
• Flat File: Data is stored in a single table. Usually suitable for less amount
of data.
• Relational: Data is stored in multiple tables and the tables are linked using a common field.
Relational is suitable for medium to large amount of data.
Database Servers:
Database servers are dedicated computers that hold the actual databases
and run only the DBMS and related software.
Typically databases available on the database servers are accessed through command line or
graphic user interface tools referred to as Frontends.
RDBMS( Relational database management system )
Data in a relational database management system (RDBMS) is organized in the
form of tables( in rows & columns).
Tables:
A table is a set of data elements (values) that is organized using a model of vertical
columns (which are identified by their name) and horizontal rows.
A table has a defined number of columns, but can have any number of rows.
Each row is identified by the values appearing in a particular column
identified as a unique key index or the key field.
This unique field is called the Primary Key (PK). A primary key is a
unique value that identifies a row in a table.
When primary key constraint is applied on one or more columns then it is
known as Composite Primary Key.
Primary Key (PK) value shouldn’t be repeated and not be NULL
NULL – is used to represent absence of data
Foreign key
A foreign key is a key used to link two tables together. This is sometimes also
called as a referencing key.
A Foreign Key is a column or a combination of columns whose values match a
Primary Key in a different table.( Means Foreign key is the Primary Key of
Another Table)
The relationship between 2 tables matches the Primary Key in one of the tables
with a Foreign Key in the second table.
Columns or Fields or Attributes:
A column is a set of data values of a particular simple type, one for each row of the
table
Rows or Records or Tuples:
A row also called a Record or Tuple represents a single, data item in a table.
In simple terms, a database table can be visualized as consisting of rows and
columns or fields.
Data types:
Datatypes are used to identify which type of data (value) we are going to
store in the database.
Data types in OpenOffice base are broadly classified into five
categories
• Numeric Types
• Alphanumeric Types
• Binary Types
• Date time
• Other Variable types
Numeric Types:
Numeric data types are used for describing numeric values for the field
used in the table of a database.
Numeric data types in a database can be used for storing information
such as mobile number, roll number, door number, year of school
admission, true or false statements, statistical values, etc.
Ex:
BOOLEAN
INTEGER
DECIMAL
FLOAT
DOUBLE
Alphanumeric Types:
Numeric data types are used for describing numeric values as well as
alphabets for the field.
Ex:
CHAR, VARCHAR
Binary Types:
Binary data types are used for storing data in binary formats. Binary data
types in a database can be using for storing photos, music files, etc. In
general, files of any format can be stored using the binary data type.
Ex:
BINARY, VARBINARY, LONGVARBINARY
Date time:
Date time data types are used for describing date and time values for the
field used in the table of a database.
Date time data types in a database can be used for storing information
such as date of birth, date of admission, date of product sale, etc.
Ex: DATE, TIME
SQL (Structured Query Language):
SQL is a standard language for storing, manipulating and retrieving data
in databases.
SQL is designed for managing data held in a relational database
management system (RDBMS)
SQL Commands:
The standard SQL commands to interact with relational databases are
CREATE, SELECT, INSERT, UPDATE, DELETE and DROP. These
commands can be classified into the following groups based on their
nature −
DDL - Data Definition Language
DDL is used to define the structures like schema, database, tables,
constraints etc.
Ex:
1. CREATE - Creates a new table, a view of a table, or other object in
the database.
2 ALTER - Modifies an existing database object, such as a table.
3 DROP - Deletes an entire table, a view of a table or other objects in
the database.
DML - Data Manipulation Language
DML is used to manipulate data. Examples of DML are insert, update and
delete statements.
Ex:
1 SELECT - Retrieves certain records from one or more tables.
2 INSERT - Creates a record.
3 UPDATE - Modifies records.
4 DELETE - Deletes records.
How to create database:
Syn: create database dbname;
Ex: create database KVMLK;
How to see existing databases
Syn: show databases;
How to Change database:
Syn: use dbname;
Ex: use KVMLK;
Now KVMLK database will be opened where you can create tables
How to see existing tables in the database
Syn: show tables;
It shows existing table names in the current database
How to create Table:
Syn:
Create table Table_Name( col_name1 datatype, col_name2 datatype,…….col_name_n
datatype );
Ex:
Create table student( rno int, name varchar(20), marks float);
How insert data into tables:
Syn:
Insert into Table_Name values( val_1,val_2,val_3……val_n);
Ex:
Insert into student values(3,”RAVI”, 45);
Above command is used insert values for all columns in the table
or
Syn:
Insert into Table_Name( col_nam_1,col_name_2…..) values(val_1,val_2,…);
Ex:
Insert into student (rno,marks) values (10,47);
Above command is used to insert data for specific columns in the table
How to see/ retrieve the data from tables:
Syn:
Select * from Table_Name;
Ex:
Select * from student;
Above command displays all columns & rows from the table
Syn:
Select col_1,col_2,….col_n from Table_Name;
Ex:
Select rno,marks from student;
Above command displays specific columns & all rows from the table
DISTINCT
This keyword is used to eliminate duplicate/redundant rows in the output
Syn:
Select distinct(col_name) from Table_Name;
Ex:
Select distinct(name) from items;
DESC
Is used to display the structure of table
Syn:
Desc Table_Name
Ex:
Desc items;
Alias NAME:
To give different names to the column while showing output
Syn:
Select col_nam as “Alias_Name” from Table_Name;
Ex:
Select name as “Item_NAME” from items;
WHERE
Is used to place conditions on rows
It filters rows based on the given condition
Ex:
Select * from items where name=”pen”;
Select * from items where price>10;
While placing a conditions on rows we can use
Relational Operators: > , < , >= , <= , = , !=
Logical Operators: and ( && ) , or ( || ) , not (!)
Ex:
select * from items where code<20 and code>15;
How Check NULL Values:
Syn:
Select * from Table_name where Col-Nam IS NULL;
Ex:
Select * from ITEMS where price IS NULL;
select * from items where price IS NOT NULL;
BETWEEN
Is used to place condition based on range
Syn:
Select * from Table_Name where Col_Nam BETWEEN Val1 and Val2 ;
Ex:
select * from items where code BETWEEN 15 and 20;
IN
Is used to place condition based on list values specified
Syn:
Select * from Table_Name where Col_Nam IN ( val1, val2,..) ;
Ex:
select * from items where code IN (15,20);
LIKE
To place condition based on pattern match
% - any number of characters
_ - any one character
To display names of items which are starting with alphabet P
Order By:
To display table data in either ascending or descending order based on specific column
SyN:
Select * from Table_Name order by Col_Nam ASC/DESC;
Ex:
Select * from items order by price;
Create table from existing table
Create table Table_Name AS select * from Table_Name where condition;
Insert Data from existing table
Insert into Table_Name select * from Table_Name where condition;
UPDATE
To modify data in the table
Syntax:
Update Table_Name SET col_nam_1=val, col_nam_2=val,.. where condition;
DELETE
To delete specific rows from the table
Syn:
Delete from Table_Name where condition;
DROP
To remove or delete table from the database
Syn:
Drop table Table_Name;
ALTER
To change the definition of the table like
- Add / delete column
- Add /remove constraints
- Re-define column like change name , data type, size
Syn:
Adding New Column:
Alter table Table_Name Add New_col Data_Type Constraint;
Removing Column from table:
Alter table Table_Name drop column_Name;
Change Column Name
Alter table Table_Name Change Old_col_Name New_Col_Name datatype;
Changing DATA Type or Constraints
Alter table Table_Name MODIFY Col_Nam New_DT New_Constraint ;
