SQL Query Example and Sample SQL Commands Queries

MYSQL Query Example

CHAR_LENGTH() Example

CHAR_LENGTH() function is used to count the character in the given String.

⇛  SELECT question, explainans, `view`, CHAR_LENGTH(explainans) AS lengt 

FROM `question` WHERE explainans IS NOT NULL AND CHAR_LENGTH(explainans) > 200 
ORDER BY `view` DESC

---

GROUP BY() Example

⇛ SELECT created_at, COUNT(*) FROM `application_for_otherexam` WHERE exam_id = '8' 

GROUP BY DATE(created_at)

⇛ SELECT created_at, COUNT(*) AS Total FROM `application_for_otherexam` 

WHERE exam_id = '8'  AND form_status='55' GROUP BY DATE(created_at)

---

General Sample MY SQL Query Example

⇛ SELECT COUNT(*) AS Total FROM `application` WHERE formstatus IN('4', '44', '55') 

AND examid='8'

⇛ SELECT school_name, code FROM `school_master` WHERE code IN('22','31','31')

⇛ SELECT CONCAT(FIRST_NAME,' ',LAST_NAME) AS WORKER_NAME FROM WORKER WHERE 

CONCAT(FIRST_NAME,' ',LAST_NAME) LIKE '%hal'

⇛ SELECT YEAR(dob), COUNT(*) AS TOTAL FROM STUDENT_DETAILS GROUP BY YEAR(DOB) 

HAVING TOTAL>10 ORDER BY YEAR(DOB) ASC

⇛ SELECT CONCAT(FIRST_NAME, ' ', LAST_NAME), SALARY FROM WORKER WHERE SALARY

BETWEEN 50000 AND 100000 

⇛ SELECT APPCOMMADDR1, LENGTH(APPCOMMADDR1) AS T1, APPCOMMADDR2, 
LENGTH(APPCOMMADDR2) AS T2, APPCOMMADDR3, LENGTH(APPCOMMADDR3) AS T3 
FROM `Data_Dump_Dept_exam_dec2019_20191203` HAVING T1>50 AND T2>50 AND T3>50

---

MYSQL Sub Queries Example

⇛ SELECT udiscode FROM schoolmaster WHERE code IN ( SELECT SCHL FROM `udise_mapping`

GROUP BY SCHL HAVING COUNT(*)>1)

---

CONCAT(), UPPER(), LOWER() Function Example

⇛ SELECT UPPER(FIRST_NAME) AS FNAME, UPPER(LAST_NAME) AS LNAME, 

CONCAT(UPPER(FIRST_NAME),' ',UPPER(LAST_NAME))AS FULLNAME FROM WORKER 

ORDER BY FULLNAME ASC

⇛ SELECT CONCAT(FIRST_NAME,' ',LAST_NAME) AS WORKER_NAME FROM WORKER WHERE 

CONCAT(FIRST_NAME,' ',LAST_NAME) LIKE '%hal'

---

SQL DATE(), YEAR(), MONTH() Function Example

⇛ SELECT CURDATE() 

⇛ SELECT YEAR(CURDATE())

⇛ SELECT MONTH(CURDATE())

⇛ SELECT DAY(CURDATE())

⇛ SELECT NOW()

⇛ SELECT YEAR(NOW())

⇛ SELECT MONTH(NOW())

⇛ SELECT HOUR(NOW())

⇛ SELECT MINUTE(NOW())

⇛ SELECT SECOND(NOW())

⇛ SELECT * FROM WORKER WHERE MONTH(JOINING_DATE) = 6 ORDER BY SALARY DESC LIMIT 2

⇛ SELECT  MONTHNAME(STR_TO_DATE(MONTH(dob),'%m')) AS mname, COUNT(*) AS totalno 

FROM `student_details` WHERE MONTH(dob) =1 GROUP BY MONTHNAME(STR_TO_DATE(MONTH(dob),'%m'))

---

Example Using Case in MYSQL Query

⇛ SELECT COUNT(
       CASE
           WHEN roll_no IS NULL
           THEN 1
           ELSE NULL
       END
   ) AS 'not_complete',
   COUNT(
       CASE
           WHEN  roll_no IS NOT NULL
           THEN 1
           ELSE NULL
       END
   ) AS 'completed'
    FROM `application`
WHERE examid =7

⇛ SELECT school_id, COUNT(
       CASE
           WHEN formstatus = '4'
           THEN 1
           ELSE NULL
       END
   ) AS 'Initiated',
   COUNT(
       CASE
           WHEN  formstatus = '45'
           THEN 1
           ELSE NULL
       END
   ) AS 'Payment Pending',
   COUNT(
       CASE
           WHEN  form_status = '55'
           THEN 1
           ELSE NULL
       END
   ) AS 'Payment Done'
    FROM `application` 
WHERE examid =7 GROUP BY school_id

⇛ SELECT COUNT(
CASE 
  WHEN gender = 'MALE'
  THEN 1
  ELSE NULL
  END 
  ) AS MALECOUNT, 
COUNT(
CASE 
  WHEN gender = 'FEMALE'
  THEN 1
  ELSE NULL
  END 
  ) AS FEMALECOUNT, COUNT(*) AS Total 
FROM `application` WHERE exam_id='8' AND form_status IN('55')

---

Using MYSQL IF statement with Example

⇛ SELECT b.centre_id,COUNT(IF(MED='TAMIL' OR  MED='ENGLISH',1,NULL)) AS TAMENG,
COUNT(IF(MED='KANNADA',1,NULL)) AS KANNADA,
COUNT(IF(MED='HINDI',1,NULL)) AS HINDI,
COUNT(IF(MED='URDU',1,NULL)) AS URDU,
COUNT(IF(MED='ARABIC',1,NULL)) AS ARABIC,
COUNT(IF(MED='PERSIAN',1,NULL)) AS PERSIAN,
COUNT(IF(MED='LATIN',1,NULL)) AS LATIN,
COUNT(IF(MED='ENGLISH',1,NULL)) AS ENGLISH FROM `dummynoexam`a
JOIN `examrollnumber` b ON a.student_id=b.student_id AND a.class_id=b.class_id 

AND a.sess_id=b.sess_id
JOIN  centremasterexam cm FORCE INDEX(cent_class_sess) ON   b.centre_id=cm.CENT 

AND b.class_id=cm.CLASS_ID AND b.sess_id=cm.SESS_ID
GROUP BY SUB,b.centre_id

⇛ SELECT COUNT(IF(statusflag = 1, 1, NULL)) AS STATUS1, COUNT(IF(statusflag=0, 1, NULL)) 
AS STATUS0, COUNT(IF(statusflag IS NULL,1, NULL)) AS STATUSNULL FROM `student`

---

MYSQL LPAD AND MID FUNCTION() USAGE and Example

SELECT `code`, MID(district_name,1,4) AS prefix, CONCAT(prefix,LPAD(`code`,4,0)) 
FROM `educational_district`

---

Set the Variable in MYSQL

SET @a:=1;  // Run this first and then run the query

SELECT e.code, s.dge_code, centre_id, rollno, student_group_id, subject_id, medium_id, @a:=@a+1 AS ID   FROM `student_pappermapping` a JOIN `student_exam_details` b ON a.student_exam_details_id =b.id
JOIN `student_details` c ON b.student_id= c.id 
JOIN `subject_list` d ON d.SUBJECT = a.subject_id AND b.class_id=d.CLASS_ID
JOIN school_master s ON c.school_id=s.id
JOIN `educational_district` e ON s.district_id=e.id
JOIN  `exam_rollnumber` r ON r.student_id=c.id AND r.class_id = b.class_id AND r.sess_id=b.sess_id
WHERE c.school_id =1345 AND b.class_id =3 AND b.sess_id= 1 AND (d.TTAG='P' OR LNKSUB <> '')  
ORDER BY e.code, dge_code, student_group_id, subject_id, medium_id

---

SELECT sr.student_id, doe.ROLLNO, doe.NAME, COUNT(*), (GROUP_CONCAT(sr.subject_id SEPARATOR ', ')) AS applied_subjects
FROM `student_revaluation` sr JOIN `dummy_no_exam` doe ON doe.student_id = sr.student_id AND doe.SUB = sr.subject_id GROUP BY sr.student_id