Chapter -1
Introduction of SQL
Structure Query Language
What is SQL?
It is a programming language specifically designed for working with Database(DBMS) :
CREATE
DELETE
REMOVE
MANIPULATE
SHARE/ACCESS
Why is SQL?
SQL is a widely popular because it offers the following advantages:
Allows users to communicate i.e, access and manipulate the database.
Allows users to retrieve data from a database.
Allows users to create, update, modify and delete the database
So, SQL is a popular language for defining the structure of a database.
SQL Terms
Data: Data is defined as figures, or information that’s stored in or used by a computers
Database: A Database is an organized collection of data/ information so that it can be easily accessed, managed and updated.
SQL Data Types
Numeric – bit, tin, smaller, int, bigint, decimal, numeric, float, real
Character/String – Char, Varchar, Test
Date/Time – Date, Time, Datetime, Timestamp, Year
Miscellaneous – Json, XML
SQL Constraints/ Rules:
SQL Command Groups:
DDL (Data Definitions Language): creations of objects/structure
DML (Data Manipulations Language): manipulations of data
DCL (Data Control Language): assignment of removal of permissions
TCL (Transaction Control Language): saving and restoring changes to a database.
Note:----- How to add comments in SQL
Same as input python type
1-line Comments: # This is one line comments
2 or More then line Comments: /* This is one to more then line comments */
DDL - (Data Definitions Language):
In this case create a structure of table and modify table parts of rows or columns, take care not delete data in any particular members of information.
DDL - (Data Definitions Language) – Create Command Syntax of DDL
CREATE TABLE employess(
emp_id INT(10) NOT NULL,
first_name VARCHAR(20),
// last_name VARCHAR(20) NOT NULL,
salary int(10) NOT NULL,
PRIMARY KEY(emp_id);
)
Note: When run all code in Workbench SQL Software in your system then create a format of table in put value of employee’s details see in demo.
Structure of table
Let’s see a video for better understanding on YouTube:
How to Download and Install Workbench for SQL
How to create table in MySQL
Full tutorial on my channel Go and see for learning.
Note: Following all Steps for Create a Database in Workbench SQL software:
Step 1: Create a Database.
Command:
create database Company;
Step 2: Create a Table in Database.
Command:
create table employees(
emp_id int not null,
first_name varchar(20),
last_name varchar(20),
salary int,
primary key(emp_id)
);
Step 3: Show the datatable format:
Command:
show select *from employees;
SELECT * FROM registr.sregsnum; // when import data
describe emp;
Step 4: Add Extra column in table of employees contact_num:
Command:
alter table employees add column contactnum int;
Note : Show data add yes or not.
Command:
select *from employees;
Step 5: Show the data which type int, varchar in row format.
Command:
describe employees;
Step 6: Rename the column contact to mobile num:
Command:
alter table employees rename column contactnum to mobilenum;
Step 6: Truncate delete all employees data in database;
Command:
truncate table employee;
Step 7: Delete all data in with database / Delete Table of employees;
Command:
Drop table employees;
So Finally, you are creating a table of structure in DDL command using and this is all basic command of DDL same as DML, DCL, etc,
command you can use.
DML - (Data Manipulations Language):
This is 2nd steps of Database in this case insert information in table 4.
DML – Insert Command:
Using insert command add elements value in table of studs more student information.
insert into studs(S_Roll, S_Name, S_Marks, S_Fees) value (82025, 'Shailu', '458',18500);
insert into studs(S_Roll, S_Name, S_Marks, S_Fees) value (82089, Annu', '558',18500);
Insert element in DDL
DML – update Command:
Using update command update elements value in table where add primary key.
update studs set S_Name='Priya' where S_Roll=82025;
update department set dept_id=3 where dept_nm='HR'; //update one constants
update element in DDL
DML –delete Command:
Using delete command delete elements value in table where add primary key also
delete from studs where S_Roll in (82045);
delete element in DDL
Complete DDL Command:
#DDL Command using full in this post
#insert into studs(S_Roll, S_Name, S_Marks, S_Fees) value (82089, 'Anuu', '458',18500);
#update studs set S_Name='Priya' where S_Roll=82025;
#delete from studs where S_Roll in (82089);
select *from studs;
DML - (Data Manipulations Language):
It is used for security purpose, where decide which members given permission for access my database of elements.
Grant:- Give the permission for data access
Example: - GRANT <privilege list /column name> ON <Relation Name/ Table Name> TO <USER>
Revoke:-Take the permission by user
REVOKE <privilege list /column name> ON <Relation Name/ Table Name> TO <USER>.
DCL - (Data Transaction Language):
It is used for security purpose, where if you have given permission by user wrong employees for data access then rollback and modified your problem.
0 Comments