Scribd
Upload
8 views5 pages

Mysql Basic Notes-3

The document outlines SQL commands for creating and managing an attendance table in a database. It includes commands for inserting attendance records, querying total working days, and counting present and absent statuses for each student. The SQL commands demonstrate basic database operations such as table creation, data insertion, and data retrieval.

Uploaded by

joyalprincess
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
8 views5 pages

Mysql Basic Notes-3

The document outlines SQL commands for creating and managing an attendance table in a database. It includes commands for inserting attendance records, querying total working days, and counting present and absent statuses for each student. The SQL commands demonstrate basic database operations such as table creation, data insertion, and data retrieval.

Uploaded by

joyalprincess
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
You are on page 1/ 5

1.

use sec_aids;
2. create table attendance(AID int not null auto_increment,id int not null,ADATE date not
null,ASTATUS varchar(10) not null ,primary key(AID));
3. show tables;

4. insert into attendance(id,ADATE,ASTATUS)values


(1,'2019-11-01','p'),
(1,'2019-11-03','a'),
(1,'2019-11-03','a'),
(1,'2019-11-04','p'),
(2,'2019-11-01','a'),
(2,'2019-11-02','a'),
(2,'2019-11-03','a'),
(2,'2019-11-04','a'),
(3,'2019-11-01','p'),
(3,'2019-11-02','p'),
(3,'2019-11-03','p'),
(3,'2019-11-04','p'),
(4,'2019-11-01','p'),
(4,'2019-11-02','p'),
(4,'2019-11-03','a'),
(4,'2019-11-04','p'),
(5,'2019-11-01','p'),
(5,'2019-11-02','p'),
(5,'2019-11-03','p'),
(5,'2019-11-04','p'),
(6,'2019-11-01','p'),
(6,'2019-11-02','p'),
(6,'2019-11-03','p'),
(6,'2019-11-04','p'),
(7,'2019-11-01','p'),
(7,'2019-11-02','p'),
(7,'2019-11-03','p'),
(7,'2019-11-04','p'),
(8,'2019-11-01','p'),
(8,'2019-11-02','p'),
(8,'2019-11-03','a'),
(8,'2019-11-04','p'),
(9,'2019-11-01','p'),
(9,'2019-11-02','a'),
(9,'2019-11-03','a'),
(9,'2019-11-04','a'),
(10,'2019-11-01','p'),
(10,'2019-11-02','p'),
(10,'2019-11-03','p'),
(10,'2019-11-04','p'),
(11,'2019-11-01','p'),
(11,'2019-11-02','p'),
(11,'2019-11-03','p'),
(11,'2019-11-04','p'),
(12,'2019-11-01','p'),
(12,'2019-11-02','a'),
(12,'2019-11-03','a'),
(12,'2019-11-04','p'),
(13,'2019-11-01','p'),
(13,'2019-11-02','a'),
(13,'2019-11-03','p'),
(13,'2019-11-04','p'),
(14,'2019-11-01','p'),
(14,'2019-11-02','p'),
(14,'2019-11-03','p'),
(14,'2019-11-04','p'),
(15,'2019-11-01','p'),
(15,'2019-11-02','a'),
(15,'2019-11-03','p'),
(15,'2019-11-04','p'),
(16,'2019-11-01','p'),
(16,'2019-11-02','p'),
(16,'2019-11-03','p'),
(16,'2019-11-04','p'),
(17,'2019-11-01','p'),
(17,'2019-11-02','p'),
(17,'2019-11-03','p'),
(17,'2019-11-04','p'),
(18,'2019-11-01','p'),
(18,'2019-11-02','p'),
(18,'2019-11-03','p'),
(18,'2019-11-04','p'),
(19,'2019-11-01','a'),
(19,'2019-11-02','a'),
(19,'2019-11-03','a'),
(19,'2019-11-04','a'),
(20,'2019-11-01','p'),
(20,'2019-11-02','p'),
(20,'2019-11-03','p'),
(20,'2019-11-04','p'),
(21,'2019-11-01','p'),
(21,'2019-11-02','p'),
(21,'2019-11-03','p'),
(21,'2019-11-04','p'),
(22,'2019-11-01','p'),
(22,'2019-11-02','p'),
(22,'2019-11-03','a'),
(22,'2019-11-04','p'),
(23,'2019-11-01','p'),
(23,'2019-11-02','p'),
(23,'2019-11-03','p'),
(23,'2019-11-04','p'),
(24,'2019-11-01','p'),
(24,'2019-11-02','a'),
(24,'2019-11-03','a'),
(24,'2019-11-04','p');
5. select *from attendance;

6. select *from students;


7. select *from attendance where id=23;

Find total working days ?

8. select count(ADATE) As Tot_Working_Days from attendance;

9. select id,count(ADATE) As Tot_Working_Days from attendance group by id;


Find No of present and Absent Using decision making?

10. select id,count(ADATE) As Tot_Working_Days,count(if(ASTATUS='p',1,NULL)) As


Tot_Present,count(if(ASTATUS='a',1,NULL)) As Tot_Absent from attendance group by id;

You might also like