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
