Most Important SQl Query - CODEHUBDEEP


SUBJECT EDUCATION
-----------------------------------------------------

Sql Query- (Different database table name & field name ) -
INSERT =
SELECT =
SELECT while loop =  
UPDATE
DELETE
MAX integer
MIN DATE
COUNT integer
SUM integer

-------------------------------------------------------
<?php

INSERT =

$name = $_POST['name'];
$email = $_POST['email'];
$mobile = $_POST['mobile'];
$INSERT  = "INSERT INTO STUDENTS(name,email,mobile) values ('$name','$email','$mobile')";
$result = $con->query($INSERT);
echo "Data Inserted Successfully";

-------------------------------------------------------

SELECT =

$STUDENTS_roll_no = $_POST['rollno'];
$class = $_POST['class'];
$select = SELECT * FROM STUDENTS WHERE rollno=$STUDENTS_roll_no and class='$class'";
$result = $con->query($select);
$data = $result->fetch_assoc();
$name =  $data['name'];
$email =  $data['email'];
$mobile =  $data['mobile'];

--------------------------------------------------

SELECT while loop  =

$roll = $_POST['thrroll'];
$query = "SELECT * FROM teachers where roll='$roll' and status='present'";
$result = $con->query($query);
while($data = $result->fetch_assoc()){
$tchr_name = $data['name'];
$tchr_email = $data['email'];
$tchr_mobile = $data['mobile'];
$tchr_subject = $data['subject'];
$tchr_roll = $data['role'];

}

----------------------------------------------

UPDATE =

$pass = $_POST['user_pass'];
$roll_no = $_POST['user_roll_no'];
$update = "UPDATE STUDENTS SET password='$pass' WHERE rollno='$roll_no'";
$result = $con->query($update);



----------------------------------------------

DELETE  =

$subject = $_GET['subject'];
$price_del = $_GET['price_del'];
$delete = "DELETE FROM SUBJECT WHERE subject_name='$subject and book_price=$price_del'";
$result = $con->query($delete);


---------------------------------------------

MAX  integer =

$book_name = $_GET['book_name'];
$writer=$_GET['writer'];
$max=" select MAX(price) from book Where book_name='$book_name' and writer='$writer'";
$res_max=$con->query($max);
$maxData = $res_max->fetch_assoc();
$maxPrice = $maxData['MAX(price)'];

---------------------------------------------

MIN DATE =
$email = $_GET['email'];
$Query = "SELECT MIN(Date) FROM Employee where email='$email'";
$result = $con->query($Query);
$mindate = $result->fetch_assoc();
$mindate1 = $mindate['MIN(Date)'];

----------------------------------------------

COUNT integer  =

$name = $_POST['name'];
$age = $_POST['age'];
$query = SELECT COUNT(name) FROM Students WHERE name='$name' and age='$age'";
$count=$con->query($query);
$cntdata = $count->fetch_assoc();
$ttl_count = $cntdata['COUNT(name)'];

-------------------------------------------------

SUM integer  =

$bkname = $_GET['book_name'];
$prices = $_GET['prices'];
$query = SELECT SUM(prices) FROM sallery WHERE bookname ='$bkname' and price>=$prices;
$price_sum = $con->query($query);
$price_sum_data = $price_sum->fetch_assoc();
$ttl_price = $price_sum_data['SUM(prices)'];




select * from employee where salary=(select Max(salary) from employee);


We can nest the above SQL query to find the second-largest salary.

select * from employee
group by salary
order by  salary desc limit 1,1;


===============================================
select * from blog where view=(select Max(view) from blog);

select * from blog
group by view
order by  view desc limit 1,1;
================================================

SELECT ProductID, ProductName, CategoryName
FROM Products
INNER JOIN Categories ON Products.CategoryID = Categories.CategoryID;



==================JOIN============================

SELECT Orders.OrderID, Customers.CustomerName
FROM Orders
INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID;

SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID
ORDER BY Customers.CustomerName;

SELECT Orders.OrderID, Employees.LastName, Employees.FirstName
FROM Orders
RIGHT JOIN Employees ON Orders.EmployeeID = Employees.EmployeeID
ORDER BY Orders.OrderID;

SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
CROSS JOIN Orders;

=================BETWEEN==============================

SELECT * FROM Products
WHERE Price BETWEEN 10 AND 20;

SELECT * FROM Products
WHERE Price NOT BETWEEN 10 AND 20;

SELECT * FROM Products
WHERE Price BETWEEN 10 AND 20
AND CategoryID NOT IN (1,2,3);

====================LIKE==================================

SELECT * FROM Customers
WHERE CustomerName LIKE 'a%';

SELECT * FROM Customers
WHERE CustomerName LIKE '%or%';

SELECT * FROM Customers
WHERE CustomerName LIKE '_r%';

SELECT * FROM Customers
WHERE CustomerName LIKE 'a__%';

SELECT * FROM Customers
WHERE ContactName LIKE 'a%o';


SELECT * FROM Customers
WHERE CustomerName NOT LIKE 'a%';


===================LIMIT=======================================

SELECT * FROM Customers
LIMIT 3;

SELECT * FROM Customers LIMIT 3 OFFSET 3;

SELECT * FROM Customers
WHERE Country='Germany'
LIMIT 3;