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
