A BRIEF INTRODUCTION ABOUT SQL BASIC CONCEPTS
SQL-structured query language
SQL- is a language used to interact with relational
database management system (RDBMS).
RDBMS- it is a software application used for create
and manage different databases.
What is a database:
Any collection of related information/ data is
called as a database.
Database can be stored in different ways ,it does
not have to be in your computer or memory, but mostly databases are stored in
computers.
Database management system (DBMS):
It is a special software application used to create
and manage different databases.
DBMS makes it more easier to handle large amount of
data.it also provides security and backup.
Import and exporting data from a database is easier
through DBMS. It provides interaction with software applications.
It is used for performimg CRUD operations in a
database.
Types of database:
Relational databases.
Non Relational databases.
1.Relational Databases/SQL database:
In relational databases data/ information are
organized into one or more tables. Each table has rows and columns. Column represent
the attributes and rows represent the object/user. A unique key identifies the
each row.
As we know relational Database Management system is used for interact with relational databases.
Some of the popular relational databases are (my SQL, postgresql,oracle).
Using SQL CRUD operations are performed in RDBMS.
2. Non relational database/no SQL database:
In non relational databases data's are organized into anything but not a traditional table like(key value pairs,Documents,Json)
Same as relational database management system to
interact with non relational Databases non relational database management
systems are used.
Popular NRDBMS are (Redis, MONGO db, dynamo db )
Implementation method:
There is no standard language for NRDBMS, every NRDBMS has their own Language
to perform CRUD operation.
What are Queries?
Query is a request made to the databases to get a
specific information from a database.
Lot of data present inside the database goal is to
get a specific data from the
databse.
Table and keys:
In a table column represent the attributes and rows
represent the user/ object.
Every object has a primary key.
Primary key: it is used to differentiate the look
alike objects in the table.it can be anything like string or an integer.
Foreign key: foreign key used to define the
relationship between two tables. A table can have multiple foreign key. Foreign
key is a primary key of an another table.
Composite key: it is a false primary key which need
two attributes.
SQL: Structured Query Language:
SQL is used for interact with relational database
management systems. You can use SQL to make RDBMS to do the things for you
like,
CRUD operations, Create and manage database, design
and create table, import and export the information.
Not all the RDBMS used the standard SQL, but basic
concept is same for everyone.
It is a hybrid language ,it is a type of four
Languages in one.
DQL: data Query language:
Used for query information which Is already stored in the database.
DDL: data definition language :
Used to define the database schema and layout.
DML: data manipulation language:
Used to insert, update , delete data from the database.
DCL: data Controlling Language:
Used for controlling the access to the data in the
database, it is used for user and permission management.
After creating a database with schema first step is
creating the tables to create we need to know the data types some of the most
used data types are,
Int – used to define whole numbers
Decimal(m,N)- used to define decimal numbers ,
m-total digits, N – total digits after decimal.
Example: decimal(4,2)=99.00
Varchar(10)- string of text with max length of 10
BLOB- used to store large objects, images are stored
Date- yyyy-mm-dd
Timestamp- yyyy-mm-dd hh:mm:ss (used for recording)
CREATING TABLES:
SQL syntax:
CREATE TABLE table name ();
Inside those parenthesis column name , data type ,
key type, and constraints will be defined.
Example:
Mysql> create table employee(id int, name
varchar(15), birth date, age int, primary key(id));
Query OK, 0 rows affected (0.47 sec)
It will create table.
To know information about the table you can use ,
DESCRIBE TABLE table name;
DELETING THE TABLES:
Syntax : DROP TABLE table name;
Will delete the entire table.
ADD/DELETE A COLUMN IN TABLE:
Syntax: ALTER TABLE table_name ADD column name datatype; (to add a column)
ALTER TABLE table_name DROP column_name; (to remove a column)
INSERT DATA INTO A TABLE:
Syntax: INSERT INTO
table name VALUES(value1 , value2);
If you want to leave any value as null,
INSERT INTO
table name (coumn1) VALUES(value1);
It will create a row with column 2 data as null.
CONSTRAINTS:
Constraints are use dfor control the data which is
inserted into a column / attribute.
NOT NULL- it will not allow the data to be null
Unique- it will not allow dublicate objects.
PRIMARY KEY- it is a combination of both Unique and
NOT null. It is also used as identifier.
DEFAULT- if the datya is null for a attribute /
column which has default constraint it will take the default data value.
Primary key AUTO INCREMENT- it will automatically
generate a primary key , you need not to enter the primary key.
UPDATING A DATA VALUE IN A TABLE:
Updating means changing the data which is already
provided.
Syntax: UPDATE table name SET
column_name=value1
WHERE
column_name= value;
For updating the multiple data values,
UPDATE table name
SET column_name=value1 colum2=value2
WHERE
column_name= value;
For updating a column in entire table,
Syntax: UPDATE
tablename SET column_name=value1
DELETE FROM A TABLE:
To delete all the rows,
DELETE from table name;
To delete specific row,
DELETE from table name WHERE column =value ;
To delete multiple rows
DELETE from table name WHERE column1 =value column2=value ;
BASIC QUERIES IN SQL:
1.SELECT:
SELECT * FROM TABLE NAME;
Select means get , * means everything
Using this sql comment you can get all the
information about the table.
To get specific column :
SELECT column name FROM table name;
SELECT column name 1 column name
2 FROM table name;
To get specific row,
SELECT * FROM table name WHERE
column=value;
You can also use other comparison operators(<,>,<=,>=,and
, or)
2.ORDER BY:
It is used to get the data in ascending or
descending order based the data type .
SELECT column name FROM TABLE NAME ORDER BY column name;
SELECT column name FROM
TABLE NAME ORDER BY column name desc;
3.LIMIT:
Used for control no of data to get,
SELECT column name FROM
table name limit 5;
4.DISTINCT:
It is used to get
what are the different Values entered in a specific column,
SELECT DISTINCT column name from table name;
5.AS:
It is used to get the specific column with different
title,
SELECT colum1 as your_title from table name;
CREATING A PRIMARY KEY AND FOREIGN KEY:
PRIMARY KEY (column)
FORIGN KEY (column) REFRENCES table name(column)
SQL FUNCTIONS:
COUNT:
If you wan to know how many rows in a table,
SELECT COUNT (*) from table name;
By condition,
SELECT COUNT (column name) from
table name WHERE column name=value;
AVERAGE:
If a column
with a data type integer you can find the avg of the column,
SELECT AVG (column name)
from table name;
You can also find average based on a condition
similar to count function.
SUM:
You can find sum of the specific rows similar to
count,
SELECT sum (column name) from Table name;
WILDCARDS:
Wild cards are used to get the information of a row
which matches the specific pattern given as query.
% will represent any no of character
_will represent only one character
UNION:
Union basically combines two select statements but
no of columns should be same in both statements
SELECT column1 from table name
UNION
SELECT colum2
from table name
Comments
Post a Comment