Database

Management
Systems

Basic SQL Operations

• RDBMS stands
for Relational Database Management System. RDBMS is
the basis for SQL, and for all modern database systems like
MS SQL Server, IBM DB2, Oracle, MySQL, and Microsoft
Access.
• The relational model consists of the following:
• Collection of objects or relations
• Set of operators to act on the relations
• Data integrity for accuracy and consistency
 Relation or Table
• The Entity information in RDBMS is stored in database
objects called tables. The table is a collection of related
data entries and it consists of columns and rows.
Components of table
• Attribute/Column/Field: The characteristics of entity are called
attribute.
• The values of these attributes are called Attribute values.
• Tuple/Record/Row: The value of multiple fields placed in the
horizontal plane.
SQL BASICS
• SQL is used to communicate with any Relational
Database.
• Its primary function is to support the definition,
manipulation and management of data in relational
database.
• It provides the syntax and semantics of data
definition and manipulation of data.
• It also provides an interface for portability of
database definition and application programs.
MySQL data Types
Different data types broken into three categories −
•Numeric
•Date and Time
•String Types.
common numeric data types−
•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. You can specify a width of up to 11
digits.
•FLOAT(M,D) − A floating-point number that cannot be unsigned. You can
define the display length (M) and the number of decimals (D). This is not
required and will default to 10,2, where 2 is the number of decimals and 10
is the total number of digits (including decimals). Decimal precision can go to
24 places for a FLOAT.
•Other- DOUBLE(M,D), DECIMAL(M,D) DECIMAL(M,D),
TINYINT, SMALLINT, MEDIUMINT, BIGINT
common Date and Time Types
•DATE − A date in YYYY-MM-DD format, between 1000-01-01 and 9999-12-
31. For example, December 30th, 1973 would be stored as 1973-12-30.
•DATETIME − A date and time combination in YYYY-MM-DD HH:MM:SS
format, between 1000-01-01 00:00:00 and 9999-12-31 23:59:59. For
example, 3:30 in the afternoon on December 30th, 1973 would be stored as
1973-12-30 15:30:00.
•TIMESTAMP − A timestamp between midnight, January 1st, 1970 and
sometime in 2037. This looks like the previous DATETIME format, only
without the hyphens between numbers; 3:30 in the afternoon on December
30th, 1973 would be stored as 19731230153000 ( YYYYMMDDHHMMSS ).
•TIME − Stores the time in a HH:MM:SS format.
•YEAR(M) − Stores a year in a 2-digit or a 4-digit format. If the length is
specified as 2 (for example YEAR(2)), YEAR can be between 1970 to 2069 (70
to 69). If the length is specified as 4, then YEAR can be 1901 to 2155. The
default length is 4.
common String Types
•CHAR(M) − A fixed-length string between 1 and 255 characters in
length (for example CHAR(5)), right-padded with spaces to the
specified length when stored. Defining a length is not required, but
the default is 1.
•VARCHAR(M) − A variable-length string between 1 and 255
characters in length. For example, VARCHAR(25). You must define a
length when creating a VARCHAR field.
•BLOB or TEXT − A field with a maximum length of 65535
characters. BLOBs are "Binary Large Objects" and are used to store
large amounts of binary data, such as images or other types of files.
Fields defined as TEXT also hold large amounts of data. The
difference between the two is that the sorts and comparisons on
the stored data are case sensitive on BLOBs and are not case
sensitive in TEXT fields. You do not specify a length with BLOB or
TEXT.
•Other- TINYBLOB or TINYTEXT, MEDIUMTEXT, LONGTEXT, ENUM
Create Table Construct
 Insert
 insert into student (roll,name,add,phon) values (113, ’Smith’,
’Biology’, 66000);

 Delete
 Remove all tuples from the student relation
 delete from student ;
Remove selected tuple
 Delete from student where rollno=123;
Retrieve Data
WHERE Condition
• SELECT column1, column2, ...FROM table_name WHERE condition;
• SELECT * FROM Customers WHERE Country=‘ghaziabad';
• SELECT column1, column2, ... FROM table_name
WHERE condition1 AND condition2 AND condition3 ...;
• SELECT column1, column2,...FROM table_nameWHERE NOT condition
;
• SELECT column_name(s) FROM table_name
WHERE column_name IN (value1, value2, ...);
• SELECT column_name(s) FROM table_name
WHERE column_name IN (SELECT STATEMENT);
• SELECT * FROM Customers
WHERE Country IN ('Germany', 'France', 'UK');
BETWEEN Operator

• SELECT column_name(s)
FROM table_name
WHERE column_name BETWEEN value1 AND value2;
• SELECT * FROM Products
WHERE Price BETWEEN 10 AND 20;
• SELECT * FROM Products
WHERE Price BETWEEN 10 AND 20
AND CategoryID NOT IN (1,2,3);
REFERENCES
[1] Abraham Silberschatz, Henry F. Korth, and Sudharshan .S,. "Database System
Concepts“

[2] [Link]

[3] [Link]
Thank
You
 Write sql queries of the following
• Create STUDENT
with column -Roll, name, add, phone
• Insert data of 5 students
• Display table
• Update name of roll 23
• Alter table –add column hobby
• Alter table -drop column roll