Loading repository data…
Loading repository data…
PratikMajage / repository
SQL_PRACTICE_QUESTIONS | NOTES. 📝 | APNA COLLAGE | SMITH INSTITUTE | FUSSION INSTITUTE | 📚 THIS REPOSITORY DOCUMENTS MY JOURNEY OF LEARNING MYSQL, ONE OF THE MOST POPULAR RELATIONAL DATABASE MANAGEMENT SYSTEMS. 🗄️
-- 18|11|24 APANA COLLAGE
create database collage;
ues collage;
create table student(
id int primary key,
name varchar(20),
age int not null
);
insert into student values (1,"Aman",26);
insert into student values (1,"Shradha",24);
select * from student;
-- DataBase Related Queries:
-- create database db_name;
-- create database if not exist db_name;
Create Database if not exist collage;
-- Drop database db_name;
drop database if exists db_name;
-- Show Databases
show databases;
-- Table Related Queries:
-- Create:
-- create table table_name(column_name1 datatype constraint, column_name2 datatype constraint);
create table student(rollno int primary key, name varchar(20));
-- Select And View all column:
select * from student;
-- Insert:
Insert into table_name(col1_v1, col2_v1), (col2_v2, col2_v2);
-- Practice Questions:
create table xyz_company;
use xyz_company;
create table employee(id int primary key, name varchar(20), salary int);
insert into employee values(101, "adam", 25000),(2, "bob", 30000),(103, "casey", 40000);
select * from employee;
-- Keys:
-- Primary Keys:
-- It is a column (or set of columns) in a table that uniquely identifies each row. (a unique id)
-- Thereis only 1Pk & it Should be Not Null.
-- Foreign Key:
-- A Foreign key is a column (or set of columns) in a table that refers to the rpimary key in another table.
-- there can be multiple FK's.
-- FK's can have duplicate & null values.
-- Constraints:
-- SQl commands are used to specify rues for data in a table.
-- Not Null: Column cannot have a null vaue
col1 int not null
-- Unique: all values in column are diffrent.
col2 int unique
-- primary key: makesa column unique & not null but used only for one.
-- id int primary key
create table temp(id int not null, primary key(id));
-- Foreign key: Prevent actions that would destroy links between tables
create table temp(cust_id int, foreign key(cust_id) references customer(id));
-- Default: Sets the Default Value of a Column.
salary int default 25000
-- Check: It can Limit the values allowed in a column
create table city(id int primary key, city varchar(20), age int, constraint age_check check (age>=18 and city = "Delhi"));
create table newTab(age int check(age >= 18));
-- Create a Sample Table:
create database college;
use collage;
create table student(rollno int primary key, name varchar(20), marks int not null, grade char, city varchar(20));
-- Insert this Data:
insert into student
(rollno, name, marks, grade, city)
values
(101, "Anil", 78, "B", "Pune"),
(102, "Rohan", 80, "A", "Mumbai"),
(103, "Kranti", 58, "C", "Nagar"),
(104, "Khushi", 39, "D", "Pune"),
(105, "Nikita", 50, "C", "Bhivandi");
-- 28|11|24 APANA COLLAGE
-- select: use to select data from the database
select * from student;
select name marks from student;
select distinct city from student;
-- clauses: condition
-- where clause: to define some conditions
select * from student where marks > 80;
select * from student where city = "Mumbai";
select * from student where marks > 80 and city = "Mumbai";
-- operators: using operators in where
-- arithmatic: + - * / %
-- comparison: = != < > <= >=
-- logical: and or not in between all like any
-- bitwise: & |
select * from student where marks > 30;
select * from student where name = "Pratik";
-- and: to check for both conditions to be true
select * from student where marks > 80 and city = "Mumbai";
-- or: to check for one of the condition to be true
select * from student where marks > 90 or city = "Mumbai";
-- between: select for a given range: inclusive values: also 80 marks and 90 marks members included
select * from student where marks between 80 and 90;
-- in: matches any value in the list
select * from student where city in ("Delhi","Mumbai");
-- not: to negate condition
select * from student where city not in ("Delhi","Mumbai");
-- limit: sets an upper limit on number of (tuples) rows to be returned
select * from student where marks > 90 limit 3;
-- order by: to sort in assending(asc) order or decending(desc) order
select * from student order by city desc;
select * from student order by city desc limit 3;
-- aggregate functions: aggreate functions perform a calculations on a set of values, and return a single value.
-- count(): count numbers
select count(name) from student;
-- max(): get maximum values
select max(marks) from student;
-- min(): get minimum values
select min(marks) from student;
-- sum: get sum of values
select sum(marks) from student;
-- avg(): get average of values
select avg(marks) from student;
-- group by clause
select city, count(rollno) from student group by city;
select city, name, count(rollno) from student group by city, name;
select city, avg(marks) from student group by city order by city, ;
select city, avg(marks) from student group by city order by avg(marks);
select city, avg(marks) from student group by city order by avg(marks) desc;
select mode, count(customer) from payment group by mode;
select grade, count(rollno) from student group by grade order by grade;
-- having clause: simmilar to where i.e. applies some condition on rows.
-- used when we we want to apply any *condition after grouping*.
-- where: rows
-- having: groups
select city, count(rollno) from student group by city having max(marks)>90;
-- general order:
select columns
from table
where condition
group by columns
having condition
order by columns asc;
select city from student where grade = "A" group by city having max(marks) >= 90 order by marks asc;
-- table related Queries:
-- update: to update existing rows
update student set grade = "0" where grade = "A";
set sql_safe_updates = 0;
update student set marks = 80 where rollno = 105;
update student set grade = "B" where marks = 80 between 90;
update student set marks = marks+1;
-- delete: to delete some existing rows
delete from student where marks < 33;
delete from student; -- all data removed.
-- foreign key:
constraint std_fk1 foreign key(id) references stident(id);
-- 01|12|24 APANA COLLAGE
use mydb;
show tables;
select * from student;
create table dept(
id int primary key,
name varchar(20)
);
insert into dept
(id, name)
values
(101, "english"),
(102, "IT");
select * from dept;
update dept
set id = 103
where id = 102;
create table teacher(
id int primary key,
name varchar(20),
deptid int,
foreign key (deptid) references dept(id)
);
insert into teacher
(id, name, deptid)
values
(1001, "Adam", 101),
(1002, "BOb", 102);
select * from teacher;
update teacher
set name = "Bob"
where id = "1002";
drop table teacher;
create table teacher(
id int primary key,
name varchar(20),
deptid int,
foreign key (deptid) references dept(id)
on update cascade
on delete cascade
);
desc dept;
desc teacher;
-- cascading for foreign key
-- on update cascade
-- when we create a foreign key using this option, it deleteshe referenceing row in the child table when the referenced row is deleted in the parent table which has a primary key.
-- on delete cascade
-- when we create a foreign key using update cascade the referencing rows are updated in the child table when the reference row is updated in the parent table which has a primary key.
-- table related queries:
-- Alter(to change the schema | design ):
-- ADD column:
-- alter table table_name add column column_name datatype constraint;
-- Drop Column:
-- alter table table_name drop column column_name;
-- RENAME column:
-- alter table table_name rename to new table_name;
-- CHANGE column (rename):
-- alter table table_name change column new_name new_datatye new_constraints;
-- MODIFY Column:
-- alter table table_name modify col_name new _datatype new_constraints;
select * from student;
alter table student
add column grade varchar(20) not null default "C";
alter table student
drop column grade;
alter table student
rename to grades;
alter table grades
rename to student;
alter table student
change column grade grd varchar(20);
alter table student
modify grd char not null;
desc student;
-- table related queries:
-- Truncate (to delete table's data)
-- truncate table table_name;
truncate table student;
update student
set grd = "A"
where grd = "C";
-- Error Code: 1175. You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column. To disable safe mode, toggle the option in Preferences -> SQL Editor and reconnect.
-- Practice Questions:
select * from student;
-- a. Change the name of column "name" to "full_name".
alter table student
change column name full_name varchar(20);
-- b. delete all the student who scored marks less than 80.alter
delete from student
where marks<30;
-- Error Code: 1175. You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column. To disable safe mode, toggle the option in Preferences -> SQL Editor and reconnect.
-- c. delete the column for garade.
alter table student
drop column grd;
-- joins in sql:
-- joins used to combine rows from two or more tables, based on a related columns between them.
-- Inner join
-- Outer join - Left join, Right join, Full join
-- Inner join:
-- Returns record that have matching values in