COMPUTER SCIENCE / INFORMATICS PRACTICES SHASHANK JAIN

DBMS & Simple Queries of SQL

RDBMS – Relational Database Management System
 SQL: Structured Query Language. Computer Programming Language (creating databases & tables)
 Database: A database is an organized collection of data and data about data (i.e. Metadata).
 DBMS: Software that contains the database and the tools to manage that database.
 Relational Databases: A database in which data is organized in the form of relations or tables.
 RDBMS: A DBMS used to manage relational databases is called RDBMS. Some popular examples
of this category are Oracle, MySQL, Sybase, Ingress, etc.
 MySQL: It is an open source RDBMS platform / software which is available free of cost.

 Benefits of RDBMS
 Reduce data redundancy(data duplication)
 Control data inconsistency (incorrect form of data)
 Facilitates sharing of data
 Centralized database can ensure data security
 Integrity can be maintained through databases

 Different Data Models

A data model refers to a set of concepts to describe the structure of a database and certain
constraints (restrictions) that the database should obey. The four data models that are used for database
management are:
 Relational Data Model -- Data represented in the form of relations or tables.
 Network Data Model – Data represented in the form of network.
 Hierarchical Data Model – Data represented in the form of hierarchy or priority.
 Object Oriented Data Model – Data represents the real world objects.

Note: An object is an identifiable entity with some characteristics and behavior. Similar objects are
conceptually collected together into meaningful groups called “classes”. For example, “Mohan is
a student” means “Mohan” object belongs to “Student” class.

DATABASE QUERY USING SQL / MYSQL PROGRAMMING 1

 COMPUTER SCIENCE / INFORMATICS PRACTICES SHASHANK JAIN

 Relational Model Terminology

 Relation: A table storing logically related data in the form of rows and columns.
 Domain: This is a pool of values from which the actual value is inserted in a given column.
 Tuple: A row of a relation is generally referred to as a tuple.
 Attribute: A column of a relation is generally referred to as an attribute.
 Degree: This refers to the number of attributes in a relation. Column Count
 Cardinality: This refers to the number of tuples in a relation. Row / Record Count
 View: It is a virtual table that does not really exist in its own right but is instead derived from one or
more underlying base tables.

DATABASE QUERY USING SQL / MYSQL PROGRAMMING 2

 COMPUTER SCIENCE / INFORMATICS PRACTICES SHASHANK JAIN

DATABASE QUERY USING SQL / MYSQL PROGRAMMING 3

 COMPUTER SCIENCE / INFORMATICS PRACTICES SHASHANK JAIN

 Types of Commands in MySQL

MySQL is a fast, reliable, scalable approach for many of the commercial RDBM’s available today. It
operates using client/server architecture in which the server runs on the machine containing the
databases and clients connect to the server over a network.
 DDL(Data Definition Language): Commands used to perform task related to table data definition like
 Creating, altering and dropping tables
 Granting and revoking databases
 Maintenance commands

 DML(Data Manipulation Language): Commands used to work on the data in the tables like
 Insertion of data in table
 Deletion of data(rows) from table
 Updation of data in the table
 Selection of data from the table
 TCL (Transaction Control Language): Commands that allow managing and controlling a transaction.
A Transaction is a complete unit of work involving many steps like
 Permanently save the changes (COMMIT)
 Permanently undo the changes (ROLLBACK)
 Creating Savepoint / Checkpoints (SAVEPOINT)
 BEGIN/START TRANSACTION
 SET Autocommit = 0 or 1;
 MySQL Elements
 Literals: A fixed data value, which may be character literal or numeric literal. The numeric literals can
be further integer literals and real literals. Eg: “Informatics Practice”, 786, -7654, etc…
 Data Types: Meant to identify the type of data and associated operation for handling it.

Both consists of 255

characters each
In decimal, p means total no.
of digits and s means decimal
digits
In integer, maximum size is
of 11 digits

 Null Values: If a column in a row contains no value, then column is said to be null. Any arithmetic
expression containing a null, always evaluates to null.
 Eg: Null + 2 = Null Null * 3 = Null Null – 5 = Null
 Comments: A comment is a text that is not executed; it is only for documentation purpose. They
can be written in 3 ways:
 Multiple line comments: Enclosed in /*………..*/
 Single line comments: Start with 2 hyphens followed by space (-- )
 Single line comments: Begin with #

DATABASE QUERY USING SQL / MYSQL PROGRAMMING 4

 COMPUTER SCIENCE / INFORMATICS PRACTICES SHASHANK JAIN

 Simple Queries of SQL:

Commands on Databases

1. Creating a database
Syntax: create database <database_name>;
Example: create database school;
create database ip;

2. To see all the existing databases on computer

Syntax: show databases;
Example: show databases;

3. Using/Opening a database
Syntax: use <database_name>;
Example: use school;

4. Delete/Drop a database
Syntax: drop database <database_name>;
Example: drop database school;

Commands on tables

5. To see all the existing tables in a database

Syntax: show tables;
Example: show tables;

6. Creating a table in MySQL

Syntax: create table <table_name>

(<columnname> <datatype> [<size>], ……);

Example: Create table student

( Rollno integer (2),
Sname varchar (25),
Class varchar (15),
Marks decimal (5, 2));
SQL Constraints -- A constraint is a condition/check applicable on a field or set of fields.
-- Not null ensures that the column cannot be left blank
-- Unique ensures that all values in a column are different
-- Default provides a default value to the column
-- Check used to check a condition for a column
-- Primary key used to uniquely identify each row of a table
-- Foreign key used to ensure referential integrity

DATABASE QUERY USING SQL / MYSQL PROGRAMMING 5

 COMPUTER SCIENCE / INFORMATICS PRACTICES SHASHANK JAIN

Example:

create table student create table activity

( Rollno integer primary key, (Activity_id integer primary key,
name varchar(20) not null, Aname varchar(20) unique,
class varchar(10), Foreign Key Rollno integer references
marks decimal check(marks > 0)); student(Rollno),
Score integer default 80);
create table bank create table transfer
( accno integer, ( Tid integer,
name varchar(15) not null, Tdate date,
address varchar(40) not null, accno integer,
balance decimal(9,2), type varchar(10),
primary key(accno)); amount decimal,
primary key(Tid),
foreign key(accno) references bank(accno));

Column Constraint: A constraint that is applied on one column is known as column constraint.
Table Constraint: A constraint that is applied at the end of the table definition, on one or more
columns is called a table constraint.
Note: Primary key and foreign key constraints can also be applied using alter table.

Create table transfer Alter table transfer

(Tid integer, add primary key(Tid);
Tdate date,
Accno integer, Alter table transfer
Type varchar(10), add foreign key(accno)
Amount decimal); references bank(accno);

7. Inserting data into tables

Syntax: Insert into <table_name> [<column list>] values (<value1>, <value2>, …);
Example: Insert into student values (1, ‘Ajay’, ‘XII Com’, 56.5);
Insert into student (Name, Class, Rollno) values (‘Preet’, ‘XII Sc’, 2);
8. Modifying the data in tables
Syntax: Update <table name> set <column name> = <new value> [where <condition>];
Example:
I. To change the marks of Rollno 1 to 60.
Update student set marks = 60 where Rollno = 1;
II. To increase marks of all students by 5.
Update student set marks = marks + 5;

DATABASE QUERY USING SQL / MYSQL PROGRAMMING 6

 COMPUTER SCIENCE / INFORMATICS PRACTICES SHASHANK JAIN

9. Deleting data from tables

Syntax: Delete from <table_name> [where <condition>];
Example:
I. To delete the record of Rollno 2.
Delete from student where Rollno = 2;
II. To delete all records.
Delete from student;
10. Viewing the structure of a table.
Syntax: Describe / desc <table_name>;
Example: desc student;
11. Altering tables: To add / modify/ drop / rename a column / renaming a table
Syntax: Alter table <table_name> add (<columnname> <datatype> [<size>], ...);
Example: Alter table student add address varchar (30);

Syntax: Alter table <table_name> modify <columnname> <newdatatype> [<newsize>];

Example: Alter table student modify name varchar (25);

Syntax: Alter table <tbname> change <oldcolname> <newcolname> <datatype> [<size>];

Example: Alter table student change addr address varchar (30);

Syntax: Alter table <table_name> drop <columnname>;

Example: Alter table student drop address;

Syntax: Alter table <table_name> rename <new_table_name>;

Example: Alter table Student rename Stud;

16. Deleting or Dropping tables

Syntax: Drop table <table_name>;
Example: Drop table student;
OR
Drop table if exists student;
17. Simple query for displaying all the data inside a table

Syntax: Select * / < column names >

From <table_name>
[ Where <condition> ]
[ Order by <columnname> ]
[ Group by <columnname> ]
[ Having <condition> ];

DATABASE QUERY USING SQL / MYSQL PROGRAMMING 7

 COMPUTER SCIENCE / INFORMATICS PRACTICES SHASHANK JAIN

Select Keywords & Clauses

 The SELECT statement is used to select data from a database.
 The data returned is stored in a result table, called the result-set.
 SELECT Syntax for selecting all the columns from a table.
Syntax: SELECT * FROM table_name;
 SELECT Syntax for selecting column1, column2 from a table.
Syntax: SELECT column1, column2,…
FROM table_name;
 DISTINCT Keyword
 The SELECT DISTINCT statement is used to return only distinct (different) values.
 Inside a table, a column often contains many duplicate values; and sometimes you
only want to list the different (distinct) values.
Syntax: SELECT DISTINCT column1, column2, ...
FROM table_name;
 WHERE Clause
 The WHERE clause is used to filter records.
 The WHERE clause extracts only those records that fulfill a specified condition.
Syntax: SELECT column1, column2, ...
FROM table_name
WHERE condition;
 Note: The WHERE clause is not only used in SELECT statement, it is also used in UPDATE
and DELETE statement.
 Operators Used With WHERE Clause

DATABASE QUERY USING SQL / MYSQL PROGRAMMING 8

 COMPUTER SCIENCE / INFORMATICS PRACTICES SHASHANK JAIN

 ORDER BY Clause
 The ORDER BY is used to sort the result-set in ascending or descending order.
 The ORDER BY keyword sorts the records in ascending order by default.
 To sort the records in descending order, use the DESC keyword.
Syntax: SELECT column1, column2, ...
FROM table_name
ORDER BY column1, column2, ... ASC | DESC;
 GROUP BY Clause
 The GROUP BY statement group rows that have the same values into summary rows, like
"find the number of customers in each country".
 The GROUP BY statement is often used with aggregate functions like (COUNT, MAX, MIN,
SUM, AVG ) to group the result by one or more columns.
Syntax: SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
ORDER BY column_name(s);
 HAVING Clause
 The HAVING clause was added to SQL because the WHERE keyword could not be used with
aggregate functions.
Syntax: SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
HAVING condition
ORDER BY column_name(s);

 Examples Based on the Use of SELECT Command

 Selecting all data
1. To display all the data in empl table.
Select * from empl;
 Selecting particular columns
2. To display ename, sal and deptno from empl table.
Select ename, sal, deptno from empl;
3. To display the job from empl table.
Select job from empl;
4. To display the different jobs available in empl table.
Select distinct (job) from empl;
 Selecting particular rows
 Using conditional operators ( = , < , > , < = , > = , <>)
5. To display all employees working in deptno 30 from empl table.
Select * from empl where deptno = 30;

DATABASE QUERY USING SQL / MYSQL PROGRAMMING 9

 COMPUTER SCIENCE / INFORMATICS PRACTICES SHASHANK JAIN

6. To display the employees who earn more than 4000.

Select * from empl where sal > 4000;
 Using logical operators ( AND, OR, NOT )

7. To display all clerks in deptno20.

Select * from empl where job= ‘clerk’ and deptno = 20;
8. To select all the employees working in deptno 10 or 30.
Select * from empl where deptno = 10 or deptno = 30;
9. To display the name and job of employees who are not working as manager.
Select ename, job from empl where not job = ‘manager’; [or job <> ‘manager’; ]

 Condition based on range

10. To display the details of employees who earn in the range of 2000 to 5000.
Select * from empl where sal between 2000 and 5000; OR
Select * from empl where sal>=2000 and sal<=5000;

 Condition based on a list

11. To display the details of employees who are working as manager, clerk or analyst.
Select * from empl where job in (‘manager’, ‘clerk’, ‘analyst’);

 Condition based on pattern matches(like used with wildcards)

%( Multiple Characters Selection), _ (Single Character Selection)

12. To display the details of employees whose name starts with ‘S’.
Select * from empl where ename like ‘S%’;

13. To display the name of employees who have the letter “A” as the second letter in their name.
Select ename from empl where ename like ‘_A%’;

14. To display the details of employees who joined in the year 1991.
Select * from empl where hiredate like ‘1991%’;
OR
Select * from empl where hiredate between ‘1991-01-01’ and ‘1991-12-31’;

 Searching for NULL

15. To display the name and hiredate of employees who do not get any commission.
Select ename, hiredate from empl where comm is NULL;

16. To display the name and hiredate of employees who get commission.
Select ename, hiredate from empl where comm is NOT NULL;

DATABASE QUERY USING SQL / MYSQL PROGRAMMING 10

 COMPUTER SCIENCE / INFORMATICS PRACTICES SHASHANK JAIN

Syntaxes Continued
18. Add a primary key constraint using Alter Command.
Syntax: Alter table <tablename>
add [Constraint <Const_name>] primary key (<Col_name>);

19. Drop a primary key constraint using Alter Command.

Syntax: Alter table <tablename> drop primary key [<Const_name>];

20. Add a foreign key constraint using Alter Command.

Syntax: Alter table <tablename>
add [Constraint <Const_name>] foreign key (<f_key_name>)
references <p_key_table_name> (<p_key_col_name>);

 Command on Joins

21. Cross Join / Cartesian Product / Cross Product

Syntax: Select * from <tablename1>, <tablename2>;
OR
Select * from <tablename1> JOIN <tablename2>;
OR
Select * from <tablename1> CROSS JOIN <tablename2>;

22. Equi Join ( The join which consists of a Condition of EQUALITY )

Syntax: Select * from <tablename1>, <tablename2>
Where <table1> . <pkey_columnname> = <table2> . <fkey_columnname>;

23. Natural Join ( The primary key & foreign key column names must be the same )
Syntax: Select * from <tablename1> NATURAL JOIN <tablename2>;

24. Use of ‘ON’ keyword ( It performs the functionality of Equi Join from Cross Join )
Syntax: Select * from <tablename1> JOIN <tablename2>
ON (<table1> . <pkey_columnname> = <table2> . <fkey_columnname>);

25. Use of ‘USING’ keyword ( It performs the functionality of Natural Join from Cross Join )
Syntax: Select * from <tablename1> JOIN <tablename2>
using (<pkey_fkey_columnname>);

26. Left Join ( Preference is given to the left table written on the join command )
,

Syntax: Select * from <tablename1> LEFT JOIN <tablename2>

ON (<table1> . <pkey_columnname> = <table2> . <fkey_columnname>);

27. Right Join ( Preference is given to the right table written on the join command )
Syntax: Select * from <tablename1> RIGHT JOIN <tablename2>
ON (<table1> . <pkey_columnname> = <table2> . <fkey_columnname>);

DATABASE QUERY USING SQL / MYSQL PROGRAMMING 11

 COMPUTER SCIENCE / INFORMATICS PRACTICES SHASHANK JAIN

28. Command for copying one table into another table with its structure and data
,

Syntax: Create table <tablename2>

Select * from <tablename1>
[ where <condition> ];

29. Command for copying the data of one table into another table

Syntax: Insert into <tablename2>

Select * from <tablename1>
[ where <condition> ];

30. Command for showing the CREATE TABLE command in MySQL.

Syntax: Show create table <tablename>;

31. Command to ENABLE or DISABLE the foreign key constraint of MySQL.

Syntax: Set foreign_key_checks = 0; # for disabling the foreign key constraint

Set foreign_key_checks = 1; # for enabling the foreign key constraint

32. Command for UNION between two tables.

Syntax: Select * from <table1>
UNION
Select * from <table2>;

DATABASE QUERY USING SQL / MYSQL PROGRAMMING 12

 COMPUTER SCIENCE / INFORMATICS PRACTICES SHASHANK JAIN

MySQL Functions
A function is a special type of predefined command that performs some operations and return some
values. The values that are provided to the functions are called as parameters and arguments.

FUNCTIONS

MULTIPLE
SINGLE ROW/ SCALAR
ROW/AGGREGATE/ GROUP

STRING NUMERIC DATE & TIME

 NUMERIC FUNCTIONS

S. No. Name / Syntax Description

POWER (x, y) Returns the value X raised to the power of Y.

1. or Example: Select Power(2, 3); ------------ 8
POW (x, y) Select Pow(3, 2); ------------ 9
Returns the square root or under root of the value.
2. SQRT(x) Example: Select Sqrt(25); --------- 5
Select Sqrt(100); -------- 10

Returns the remainder by dividing X with Y.

3. MOD(x, y) Example: Select Mod(3, 2); --------- 1
Select Mod(3, 5); --------- 3

DATABASE QUERY USING SQL / MYSQL PROGRAMMING 13

 COMPUTER SCIENCE / INFORMATICS PRACTICES SHASHANK JAIN

S. No. Name / Syntax Description

Rounds the argument X to nearest integer value.

4. ROUND(x) Example: Select Round(55.65); --------- 56
Select Round(55.49); --------- 55
Rounds the argument X to Y decimal places.
5. ROUND(x, y) Example: Select Round(55.65, 1); --------- 56.7
Select Round(55.49, -1); --------- 60
Truncates the argument X to Y decimal places.
6. TRUNCATE(x, y) Example: Select Truncate(55.65, 1); --------- 56.6
Select Truncate(55.49, -1); --------- 50
Ceil converts the argument X to next integer value.
7. CEIL(x) Example: Select Ceil(2.23); ------------ 3
Select Ceil(432); ------------ 432
Floor converts the argument X to previous integer
value.
8. FLOOR(x)
Example: Select Floor(2.23); ------------ 2
Select Floor(42); ------------ 42

 STRING FUNCTIONS

S. No. Name / Syntax Description

It returns the length of the string in numeric.

1. LENGTH(str) Example: Select Length(‘abc 123’) ------ 7
Select Length(“$#@25Aa”) ----- 7

It will combine all the strings in a single string.

2. CONCAT(str1, str2, …) Example: Select Concat(‘A’, ‘1’, ‘bb’); --- ‘A1bb’
Select Concat(‘Aman’,‘123’); ---- ‘Aman123’

LOWER(str) Converts the uppercase alphabets into lowercase.

3. OR Example: Select Lower(‘AB12ab’); ---- ‘ab12ab’
LCASE(str) Select Lcase(‘AmaN’); ---- ‘aman’

DATABASE QUERY USING SQL / MYSQL PROGRAMMING 14

 COMPUTER SCIENCE / INFORMATICS PRACTICES SHASHANK JAIN

S. No. Name / Syntax Description

UPPER(str) Converts the lowercase alphabets into uppercase.

4. OR Example: Select Upper(‘AB12ab’); ---- ‘AB12AB’
UCASE(str) Select Ucase(‘AmaN’); ---- ‘AMAN’

Removes the leading and trailing extra spaces.

5. TRIM(str) Example: Select Trim(‘ B12 ’); ---- ‘B12’
Select Trim(‘ AmaN Jain ’); ---- ‘Aman Jain’

Removes the leading extra spaces.

6. LTRIM(str) Example: Select Ltrim(‘ B12 ’); ---- ‘B12 ’
Select Ltrim(‘ Ani Jain ’); ---- ‘Ani Jain ’

Removes the trailing extra spaces.

7. RTRIM(str) Example: Select Rtrim(‘ B12 ’); ---- ‘ B12’
Select Rtrim(‘ AmaN Jain ’); ---- ‘ Aman Jain’

Returns the first X characters from the string.

8. LEFT(str, x) Example: Select Left(‘AB12 abcd’, 3); ---- ‘AB1’
Select Left(‘AB12 abcd’, 6); ---- ‘AB12 a’

Returns the last X characters from the string.

9. RIGHT(str, x) Example: Select Right(‘AB12 abcd’, 3); ---- ‘bcd’
Select Right(‘AB12 abcd’, 6); ---- ‘2 abcd’

Returns the position of first occurrence of the

10. INSTR(str, substr) substring substr in the string str.
Example: Select Instr(‘abc 12 abc 21’, ‘ab’); ---- 1

Returns Y characters starting from Xth character of

MID(str, x, y) the string str.
OR
If Y argument is missing, then it starts from Xth
11. MID(str, x)
OR
position, and the rest of the string is returned.
SUBSTR(str, x, y) If X is negative, the beginning of the substring is
the Xth Character from the end of the string.

DATABASE QUERY USING SQL / MYSQL PROGRAMMING 15

 COMPUTER SCIENCE / INFORMATICS PRACTICES SHASHANK JAIN

 DATE & TIME FUNCTION

S. No. Name / Syntax Description

Returns the current date and time in

1. NOW( ) ‘YYYY-MM-DD HH:MM:SS’ format.
Example: Select Now( );

Returns the current date and time in

2. SYSDATE( ) ‘YYYY-MM-DD HH:MM:SS’ format.
Example: Select Sysdate( );

Returns the current date in ‘YYYY-MM-DD’ or

3. CURDATE( ) ‘YYYYMMDD’ format.
Example: Select Curdate( );

DATE(expr) Returns date and time from the given expression.

4. OR Example: Select Date(‘2001-02-24 20:45:10’);
TIME(expr) Select Time(‘2001-02-24 20:45:10’);

DAY(expr) Returns the numeric Day of Month, Month and

OR Specified Year from the date.
5. MONTH(expr) Example: Select Day(‘2020-02-24’); ----- 24
OR Select Month(‘2020-02-24’); ----- 02
YEAR(expr) Select Year(‘2020-02-24’); ----- 2020
Returns the Name of the Week Day for the
specified date.
6. DAYNAME(expr)
Example: Select Dayname(‘2020-02-24’); ----
Sunday
Returns the Day of the Week in numeric form for
7. DAYOFWEEK(expr) the specified date.
Example: Select Dayname(‘2021-02-24’); ---- 2

Returns the day of the year from 1 to 366.

8. DAYOFYEAR(expr)
Example: Select DayOfYear(‘2021-02-24’); --- 55

DATABASE QUERY USING SQL / MYSQL PROGRAMMING 16

 COMPUTER SCIENCE / INFORMATICS PRACTICES SHASHANK JAIN

 GROUP / AGGREGATE FUNCTIONS

S. No. Name / Syntax Description

1. SUM ( col_name ) Returns the sum of all the values in a column.

2. AVG ( col_name ) Returns the average of all the values in a column.

3. MAX ( col_name ) Returns the maximum value in a column.

4. MIN ( col_name ) Returns the minimum value in a column.

5. COUNT( * ) It will count all the records/rows of the table.

6. COUNT( col_name ) It will count all the not null values of the column.

 SOME KEY TERMS:

DATABASE QUERY USING SQL / MYSQL PROGRAMMING 17

 COMPUTER SCIENCE / INFORMATICS PRACTICES SHASHANK JAIN

Differences & Similarities

DDL DML

Data Definition Language Data Manipulation Language

Works on the structure of the table Works on the data of the table

Consists of commands like Alter, Create & Consists of commands like Insert, Update,
Drop Delete & Select

CHAR VARCHAR

Work on the String or text values Work on the String or text values

Consists of 0 to 255 characters Consists of 0 to 255 characters

Fixed length data type Variable length data type

Maximum memory wastage No memory is wasted

Eg: Gender char ( 5 ) Eg: Gender varchar ( 5 )

Primary Key Unique Key

Used for uniquely identifying of rows Used for uniquely identifying of rows

Cannot be null Can be null

Join two or more than two tables together Unique accessing of values of rows

Work as a table and column constraint Work as a column constraint

Drop Delete

DDL type of command DML type of command

Used to delete complete table or database Used to delete data of a table

Works with alter command Works independently

Works upon the structure of the table Works upon the data of the table

DATABASE QUERY USING SQL / MYSQL PROGRAMMING 18

 COMPUTER SCIENCE / INFORMATICS PRACTICES SHASHANK JAIN

Alter Update

DDL type of command DML type of command

Used to modify the structure of the table Used to modify the data of the table

Keywords used are add, modify, change and

Keyword used is set
drop

Round Truncate

Numeric function in MySQL Numeric function in MySQL

Rounding off a value in nearest integer or Cut off the given value in integer or given
given value value

Eg: Select round ( 39.567, 1 ); Eg: Select truncate ( 39.567, 1);

Default Check

Constraint in MySQL Constraint in MySQL

Used to set default values to every cell of a

Used to check values before inserting in a table
row

Eg: Grade char ( 2 ) default ‘D’ Eg: Marks decimal(5, 2) check(marks > 33)

Primary Key Foreign Key

Constraint in SQL Constraint in SQL

Helps in joining the tables only without unique

Helps in unique identification of rows
identification

Only one in a table Can be more than one in a table

Created in parent table Created in child table

DATABASE QUERY USING SQL / MYSQL PROGRAMMING 19

 COMPUTER SCIENCE / INFORMATICS PRACTICES SHASHANK JAIN

 How null value is different from zero (0)?

o Null means no value or blank value whereas zero is an integer value.
o Any arithmetic calculation with null always results in null.
Eg: Null + 2 = Null 0+2=2
Null * 3 = Null 0*3=0
Null – 5 = Null 0 – 5 = -5

Practical Commands of MySQL

DATABASE QUERY USING SQL / MYSQL PROGRAMMING 20

 COMPUTER SCIENCE / INFORMATICS PRACTICES SHASHANK JAIN

DATABASE QUERY USING SQL / MYSQL PROGRAMMING 21

 COMPUTER SCIENCE / INFORMATICS PRACTICES SHASHANK JAIN

 Numeric Functions

DATABASE QUERY USING SQL / MYSQL PROGRAMMING 22

 COMPUTER SCIENCE / INFORMATICS PRACTICES SHASHANK JAIN

 Date & Time Functions

DATABASE QUERY USING SQL / MYSQL PROGRAMMING 23

 COMPUTER SCIENCE / INFORMATICS PRACTICES SHASHANK JAIN

 String Functions

DATABASE QUERY USING SQL / MYSQL PROGRAMMING 24

 COMPUTER SCIENCE / INFORMATICS PRACTICES SHASHANK JAIN

CBSE Questions & Assignments on MYSQL

DATABASE QUERY USING SQL / MYSQL PROGRAMMING 25

 COMPUTER SCIENCE / INFORMATICS PRACTICES SHASHANK JAIN

DATABASE QUERY USING SQL / MYSQL PROGRAMMING 26

 COMPUTER SCIENCE / INFORMATICS PRACTICES SHASHANK JAIN

DATABASE QUERY USING SQL / MYSQL PROGRAMMING 27

 COMPUTER SCIENCE / INFORMATICS PRACTICES SHASHANK JAIN

DATABASE QUERY USING SQL / MYSQL PROGRAMMING 28

 COMPUTER SCIENCE / INFORMATICS PRACTICES SHASHANK JAIN

code of all the

DATABASE QUERY USING SQL / MYSQL PROGRAMMING 29

 COMPUTER SCIENCE / INFORMATICS PRACTICES SHASHANK JAIN

Product

DATABASE QUERY USING SQL / MYSQL PROGRAMMING 30

 COMPUTER SCIENCE / INFORMATICS PRACTICES SHASHANK JAIN

DATABASE QUERY USING SQL / MYSQL PROGRAMMING 31

 COMPUTER SCIENCE / INFORMATICS PRACTICES SHASHANK JAIN

DATABASE QUERY USING SQL / MYSQL PROGRAMMING 32

 COMPUTER SCIENCE / INFORMATICS PRACTICES SHASHANK JAIN

DATABASE QUERY USING SQL / MYSQL PROGRAMMING 33

 COMPUTER SCIENCE / INFORMATICS PRACTICES SHASHANK JAIN

DATABASE QUERY USING SQL / MYSQL PROGRAMMING 34

 COMPUTER SCIENCE / INFORMATICS PRACTICES SHASHANK JAIN

DATABASE QUERY USING SQL / MYSQL PROGRAMMING 35

 COMPUTER SCIENCE / INFORMATICS PRACTICES SHASHANK JAIN

DATABASE QUERY USING SQL / MYSQL PROGRAMMING 36

 COMPUTER SCIENCE / INFORMATICS PRACTICES SHASHANK JAIN

DATABASE QUERY USING SQL / MYSQL PROGRAMMING 37

 COMPUTER SCIENCE / INFORMATICS PRACTICES SHASHANK JAIN

DATABASE QUERY USING SQL / MYSQL PROGRAMMING 38

 COMPUTER SCIENCE / INFORMATICS PRACTICES SHASHANK JAIN

DATABASE QUERY USING SQL / MYSQL PROGRAMMING 39