Unit-3 Review of Database Concepts & SQL

Database : Database is an organized collection of interrelated data that serves many applications.

A database has the following properties:

A database is a representation of some aspect of the real world also called miniworld. Whenever there are changes in this miniworld they are also reflected in the database.
It is designed, built and populated with data for specific purpose.
It can be of any size and complexity.
It can be maintained manually or it may be computerized.

Database management systems enable users to securely, efficiently, and quickly share data throughout an organization. A data management system offers quicker access to more accurate data by quickly responding to database requests.

Use of Database in Real Life Applications: Banking, Online shopping, Student Management System, Fees Management system, Library Management System.

Data/character: is the smallest unit of file organization which is represented in the form of a bit that may either 0 or 1. 8 bits make a byte that represents a character in a computer.

Field: is a set of characters that are used together to represent data elements. It is also termed as data item. A specific data item within a record is known as a field. For eg. Rollno, admo_no, Name, Class, Marks are the fields in a student's record.

Record: A collection of fields is termed as a Record.

File: A collection of logically related records is called a File. A file is also termed as Table or a Relation. A table has rows and columns, Where row represent record or tuple and columns represent attributes or fields.

Database : Database is an organized collection of interrelated data that serves many applications.

A database has the following properties:

A database is a representation of some aspect of the real world also called miniworld. Whenever there are changes in this miniworld they are also reflected in the database.
It is designed, built and populated with data for specific purpose.
It can be of any size and complexity.
It can be maintained manually or it may be computerized.

A DBMS is a software that creates , accesses and manages database. It helps to store and find data easily. It is a general purpose software system that facilitates the process of defining, constructing and manipulating databases for various applications.

Need for a Database Management System

Advantages of DBMS

Control Data Redundancy – Duplication of data leads to wastage of storage space. A DBMS eliminates Data redundancy by integrating the files so that multiple copies of the same data are not stored..
Data Inconsistency – DBMS provides data conistency to a large extent as the changes made at one place are reflected at all other places or to all the users..
Sharing the Database – Allow multiple users to access the database at the same time.
Reduced Programming Efforts: A DBMS saves a lot of programming effeort since a user need not write programs for query processing involving several table of files, report Generation, addition, modification and deletion of data etc.
Database Enforces Standards: DBMS ensures all applicable standards like format, documentation etc.
Improved Data Integrity: Data Integrity refers to the validity and consistency of store data as it checks for the correct information to be entered by the user in the correct format.
Privacy and Security: Data security refers to protection of data against unauthorized access.
User Friendly – Because of its user-friendly interface, it reduces users’ dependence on computer specialists to carry out various data-related actions in a DBMS.

Relational Database

A collection of data elements with pre-established relationships between them make up a relational database. These things are arranged in a series of tables with rows and columns. To store data about the things that will be represented in the database, tables are utilised.

In relational model,

A row is called a Tuple.
A column is called an Attribute.
A table is called as a Relation.
The data type of values in each column is called the Domain.
The number of attributes in a relation is called the Degree of a relation.
The number of rows in a relation is called the Cardinality of a relation.

Entity : An Entity is an object which can be distinctly identified. for ex. Student entity, employee entity, item entity etc. Entity becomes the name of the table.

Attributes/ Data item/ Field: An attribute (column) is a set of values of a particular type. A table conists of several records which can be broken into several entities knowns as fields or attributes or column.

Tuple: Each row in a table is known as a tuple/ record. A record in a table represents a set of related data.

Table/ Relation: A table is a collection of logically related records.

Cardinality of Relation: It is the total number of records or tuples in the relation.

Degree of Relation: It is the total number of columns or attributes in the relation is known as Degree of relation.

Domain of Relation: It is the set of all possible values that an attribute may contain. for ex.

Age: Set of integers between 18 and 65 (Domain: {18, ..., 65}).
Gender: Set of permitted values: {'Male', 'Female', 'Other'}.
Salary: Set of all positive float numbers.
Phone Number: A 10-digit numeric string format

DATABASE KEYS: Keys allows us to identify an attributes or a set of attributes on the basis of which a tableis identified. They are used to establish and identify relation between two or more tables. the different type of keys in an RDBMS.

Primary Key: A primary key is an attribute or a group of attributes that uniquely identify tuples within the relation. A tabke can only have one primary key.

Candidate Key: A Candidate key is one that is capable of becoming the Primary Key.

Alternate Key: A candidate key that is not chosen as the primary key is called an alternate key.

Foreign Key: A non-key attribute whose value is derived from the primary key of another table is known as foreign key in current table. In other words, a primary key in some other table having relationship with the current table.

Introduction to SQL

SQL is a standard language for accessing and manipulating databases.

SQL stands for Structured Query Language
SQL lets you access and manipulate database
SQL can execute queries against a database
SQL can retrieve data from a database
SQL can insert records in a database
SQL can update records in a database
SQL can delete records from a database
SQL can create new databases
SQL can create new tables in a database
SQL can create stored procedures in a database
SQL can create views in a database

Advantages of SQL

Some advantages of SQL are as follows:

Faster Query Processing: Large amount of data is retrieved quickly and efficiently. Operations like Insertion, deletion, manipulation of data is also done in almost no time.
No Coding Skills: For data retrieval, large number of lines of code is not required. All basic keywords such as SELECT, INSERT INTO, UPDATE are used and also the syntactical rules are not complex in SQL, which makes it a user-friendly language.
Standardized Language: Due to documentation and long establishment over years, it provides a uniform platform worldwide to all its users.
Portable: It can be used in programs in PCs, server, laptops independent of any platform. Also, it can be embedded with other applications as per need/requirement/use.
Interactive Language: Easy to learn and understand, answers to complex queries can be received in seconds.
Multiple data views: One of the advantages of SQL is its ability to provide multiple data views . This means that SQL allows users to create different views or perspectives of the data stored in a database, depending on their needs and permissions.
Scalability: SQL databases can handle large volumes of data and can be scaled up or down as per the requirements of the application.
Security: SQL databases have built-in security features that help protect data from unauthorized access, such as user authentication, encryption, and access control.
Data Integrity: SQL databases enforce data integrity by enforcing constraints such as unique keys, primary keys, and foreign keys, which help prevent data duplication and maintain data accuracy.
Backup and Recovery: SQL databases have built-in backup and recovery tools that help recover data in case of system failures, crashes, or other disasters.
Data Consistency: SQL databases ensure consistency of data across multiple tables through the use of transactions, which ensure that changes made to one table are reflected in all related tables.
Not Case Sensitive: SQL is not a Case Sensitive Language.

Classification of SQL statements:

DDL (Data Definition Language ) Commands: The DDL command lets us define the database structure and its related operations.

For Ex.

Create database / Create table: Creates as new Database or table

Alter Table: modifies the columns of a table.

Drop table / Database : Deletes a database/ table

Rename : Rename a table

DML (Data Manipulation Language) Commands: The DML that helps a user to access or manipulate data.

For Ex.

INSERT Adds new rows (records) of data into a table. INSERT INTO table_name (column1, column2) VALUES (value1, value2);

UPDATE Modifies existing data within a table. The WHERE clause specifies which records to update. UPDATE table_name SET column1 = value1 WHERE condition;

DELETE Removes existing rows from a table. The WHERE clause specifies which records to delete. DELETE FROM table_name WHERE condition;

DQL (Data Query Language): DQL helps a user to access or retrieve data. It contains only one command to retrieve data from the tables of the database.

Difference between DDL and DML

MySQL
My SQL is the open source RDBMS.

Advantages of MySQL

* Reliability

* Modifiable

* Multi Platform support

* Integrity

*Authorization

SQL Data Types

Difference between CHAR and VARCHAR

SQL Commands

DDL Commands

1. Create database: is used to create a database in MySQL.

syntax:

CREATE DATABASE <DATABASE NAME>;

EX.

create database school;

2. Opening Database: Once a database has been created , we need to open is to work on it. For this USE command is required.

syntax:

USE <DATABASE NAME>;

EX.

use school;

3. Removing Databases: To delete a database physically with all its tables, DROP command is used.

Syntax:

Drop Database <Database Name>;

Ex. Drop Database School;

4. Creating A Table:

The CREATE command is used to create a table in a database. Tables are organised in rows and columns and eac table must have a name.

Syntax:

CREATE TABLE <TABLENAME>

(

<COL_NAME 1 <DATATYPE> [(SIZE)] , (CONSTRAINTS , if any),

<COL_NAME 2 <DATATYPE> [(SIZE)],

<COL_NAME 3 <DATATYPE> [(SIZE)])

);

for ex.

create table student

(

rollno integer not null primary key,

name varchar (15) not null,

gender char(1),

marks integer(11),

DOB date()

);

5. Viewing a Table:

Show tables: It displays names of all the tables of the current database.

syntax:

Show tables;

6. Viewing a Table Structure: To view a table structure DEScribe or DESC command is used.

Syntax:

Describe <Tablename>;

Ex.

Describe student;

7. ALTER TABLE: Alter table command is used to modify the structure of a table by modifying the definition of its columns. It can be used to perform addition/ deletion / modification/ removing a column or addition/ deleteing Primary Key constraints .

a. To add a column with NULL value:

Syntax : Alter Table <tablename> Add (<col_name><data type> size);

ex.

Alter table student Add (Mobile integer);

Note: NULL values will be inserted in a newly added column. UPDATE command can be used to supply data to this column.

To add a Column with default value:

Ex. Alter Table Student Add (city char(6) Default " Delhi");

b.) Modifying an existing Column Definition:

The MODIFY clause can be used with ALTER TABLE command to change the data typem size and contraint related to any column of the table.

Syntax:

Alter table Student MODIFY (Name varchar(25));

this above command is modifying the size of the Name.

c.) Rename a column:

The exixting column in a relation can be renamed using ALTER Table command.

Ex.

Alter table student change city state varchar(10);

This above command change the attaribute City to State.

d.) Delete a Column:

Syntax:

Alter Table <tablename> drop <column name>;

Ex.:

Alter Table Student Drop State;

The above command will delete the entire Column State.

e.) Adding and deleteing Primary Key Constraints:

We can add or delete Primary Key constraint using ALTER Table command if the table has already created.

syntax:

Alter Table <TableName> Add Primary Key <Column name>;

Ex. Alter table Student Add Primary key (Adm_no);

Alter Table <TableName> drop Primary key;

ex.: Alter Table Student drop Primary key;

8. Drop Table Command: Drop table command is used to remove a table permanently. If you drop a table, all the rows and its structure also gets deleted. Once a table is dropped, we canot get it back . This Command will compeletely destroy the table structure.

Syntax:

Drop Table <table_name>;

for ex.

Drop Table Student;

This command will permanently remove tha table STUDENT from the database SCHOOL.

DML Commands:

Data Manipulation using a database means either inserting of new data, removal of exixting data or modification of exixting data in the database.

1. Inserting data into a table: The INSERT INTO command is used a new record/ row/ tuple in a table.

Syntax :

insert into <tablename> values ( value1, value2.......);

Ex.

insert into Student values(1,"Rudra", 'M', 93, '2000-11-17');

Ex.

insert into Student (rollno, name, gender, marks,DOB) values(1,"Rudra", 'M', 93, '2000-11-17');

Values can also be inserted in some specific columns

Ex.

insert into Student (rollno, name, gender, marks,DOB) values(1,"Rudra", 'M');

The above statement shall insert the values in rollno, name and gender and Marks , DOB will be filled with NULL values.

inserting NULL vaues into a table:

insert into Student (rollno, name, gender, marks,DOB) values(1,"Rudra", 'M',NULL, NULL);

2.Modifying data in a table:

To modify data in a table , UPDATE command will be used.

Syntax:

UPDATE <tablename> set <column name> =<value1> , <column name2>=<value> where <condition>;

ex.

UPDATE student set marks =70 where adm_no=1;

updating NULL Values:

ex. Update student set marks=NULL where roll_no=1;

ex.

Update student set marks=NULL where (roll_no=1 or roll_no=2);

ex.

Update student set marks=Marks +10;

3. Removing Specific data from a table:

Syntax:

DELETE from <table_name> where <condition>;

ex.

DELETE from student where roll_no=1;

ex. DELETE * from student;

(delete all the records)

3. DQL: Data Query Language contains only one command that is select:

Ex.

1. Select * from Student;

displays all the records.

2. Select Roll_no , name * from Student;

displays roll_no and name of all the records.

3. Select Roll_no , name * from Student where marks>=90;

displays roll_no and name of those records who satisty the condition.

4. Select DISTINCT marks from Student;

Eliminates duplicate rows.

Select distinct stream from student;

5. Select 5+10 from dual;

6. Select Roll_no , name * from Student where marks between 60 and 80;

7. Select Roll_no , name * from Student where marks not between 60 and 80;

8. Select Roll_no , name * from Student where marks between 60 and 80;

9. Select Roll_no , name * from Student where marks in ( 60,70,80);

10. Select Roll_no , name * from Student where marks not in ( 60,70,80);

11. Select * from Student where name like "A%";

12. Select * from Student where name not like "A%";

Search This Blog

veenaIPnotes