Database - mysql queries -part2

Enter password: *****
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.27-community-nt

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create database adi;
Query OK, 1 row affected (0.09 sec)

mysql> use adi;
Database changed
mysql> create table Staff
    -> (StaffNo char(3) primary key,
    -> FName char(20),
    -> LName char(20),
    -> Position char(15),
    -> Gender char(10),
    -> DOB Date,
    -> Salary decimal(8,2));
Query OK, 0 rows affected (0.16 sec)

mysql> insert into Staff values
    -> ('102','Mukhlas','Emlia','Manager','Female','1965-06-01','3000.00');
Query OK, 1 row affected (0.05 sec)

mysql> insert into Staff values
    -> ('145','Ann','Bandar','Assistant','Female','1960-11-10','1200.00');
Query OK, 1 row affected (0.03 sec)

mysql> insert into Staff values
    -> ('216','Daudi','Fayadi','Supervisor','Male','1970-02-19','1800.00');
Query OK, 1 row affected (0.02 sec)

mysql> insert into Staff values
    -> ('235','Anis','Harun','Assistant','Female','1968-05-24','1150.00');
Query OK, 1 row affected (0.03 sec)

mysql> insert into Staff values
    -> ('254','Julie','Khan','Manager','Female','1965-11-13','2000.00');
Query OK, 1 row affected (0.03 sec)

mysql> insert into Staff values
    -> ('236','Hashim','Kamal','Supervisor','Male','1966-12-16','1900.00');
Query OK, 1 row affected (0.01 sec)

mysql> insert into Staff values
    -> ('311','Foo','Fatt','Supervisor','Male','1978-05-17','1850.00');
Query OK, 1 row affected (0.02 sec)

mysql> insert into Staff values
    -> ('324','Dubbu','Ibrahim','Manager','Male','1968-08-18','2450.00');
Query OK, 1 row affected (0.02 sec)

mysql> select*
    -> from Staff;
+---------+---------+---------+------------+--------+------------+---------+
| StaffNo | FName   | LName   | Position   | Gender | DOB        | Salary  |
+---------+---------+---------+------------+--------+------------+---------+
| 102     | Mukhlas | Emlia   | Manager    | Female | 1965-06-01 | 3000.00 |
| 145     | Ann     | Bandar  | Assistant  | Female | 1960-11-10 | 1200.00 |
| 216     | Daudi   | Fayadi  | Supervisor | Male   | 1970-02-19 | 1800.00 |
| 235     | Anis    | Harun   | Assistant  | Female | 1968-05-24 | 1150.00 |
| 236     | Hashim  | Kamal   | Supervisor | Male   | 1966-12-16 | 1900.00 |
| 254     | Julie   | Khan    | Manager    | Female | 1965-11-13 | 2000.00 |
| 311     | Foo     | Fatt    | Supervisor | Male   | 1978-05-17 | 1850.00 |
| 324     | Dubbu   | Ibrahim | Manager    | Male   | 1968-08-18 | 2450.00 |
+---------+---------+---------+------------+--------+------------+---------+
8 rows in set (0.03 sec)

mysql> select StaffNo,FName,LName,Position,Gender,DOB Date from Staff;
+---------+---------+---------+------------+--------+------------+
| StaffNo | FName   | LName   | Position   | Gender | Date       |
+---------+---------+---------+------------+--------+------------+
| 102     | Mukhlas | Emlia   | Manager    | Female | 1965-06-01 |
| 145     | Ann     | Bandar  | Assistant  | Female | 1960-11-10 |
| 216     | Daudi   | Fayadi  | Supervisor | Male   | 1970-02-19 |
| 235     | Anis    | Harun   | Assistant  | Female | 1968-05-24 |
| 236     | Hashim  | Kamal   | Supervisor | Male   | 1966-12-16 |
| 254     | Julie   | Khan    | Manager    | Female | 1965-11-13 |
| 311     | Foo     | Fatt    | Supervisor | Male   | 1978-05-17 |
| 324     | Dubbu   | Ibrahim | Manager    | Male   | 1968-08-18 |
+---------+---------+---------+------------+--------+------------+
8 rows in set (0.00 sec)

mysql> select StaffNo,FName,LName,Salary from Staff;
+---------+---------+---------+---------+
| StaffNo | FName   | LName   | Salary  |
+---------+---------+---------+---------+
| 102     | Mukhlas | Emlia   | 3000.00 |
| 145     | Ann     | Bandar  | 1200.00 |
| 216     | Daudi   | Fayadi  | 1800.00 |
| 235     | Anis    | Harun   | 1150.00 |
| 236     | Hashim  | Kamal   | 1900.00 |
| 254     | Julie   | Khan    | 2000.00 |
| 311     | Foo     | Fatt    | 1850.00 |
| 324     | Dubbu   | Ibrahim | 2450.00 |
+---------+---------+---------+---------+
8 rows in set (0.00 sec)

mysql> select FName,LName from Staff Where StaffNo=254;
+-------+-------+
| FName | LName |
+-------+-------+
| Julie | Khan  |
+-------+-------+
1 row in set (0.03 sec)

mysql> select StaffNo,DOB,LName from Staff where Gender='Female';
+---------+------------+--------+
| StaffNo | DOB        | LName  |
+---------+------------+--------+
| 102     | 1965-06-01 | Emlia  |
| 145     | 1960-11-10 | Bandar |
| 235     | 1968-05-24 | Harun  |
| 254     | 1965-11-13 | Khan   |
+---------+------------+--------+
4 rows in set (0.03 sec)

mysql> select StaffNo,FName,DOB from Staff where Position='Supervisor';
+---------+--------+------------+
| StaffNo | FName  | DOB        |
+---------+--------+------------+
| 216     | Daudi  | 1970-02-19 |
| 236     | Hashim | 1966-12-16 |
| 311     | Foo    | 1978-05-17 |
+---------+--------+------------+
3 rows in set (0.00 sec)

mysql> select F.StaffNo,F.FName,S.StaffNo,S.FName,F.Gender from Staff F,Staff S
where F.Gender=S.Gender AND F.Staffno<S.Staffno ORDER By Gender;
+---------+---------+---------+--------+--------+
| StaffNo | FName   | StaffNo | FName  | Gender |
+---------+---------+---------+--------+--------+
| 145     | Ann     | 235     | Anis   | Female |
| 235     | Anis    | 254     | Julie  | Female |
| 145     | Ann     | 254     | Julie  | Female |
| 102     | Mukhlas | 145     | Ann    | Female |
| 102     | Mukhlas | 235     | Anis   | Female |
| 102     | Mukhlas | 254     | Julie  | Female |
| 311     | Foo     | 324     | Dubbu  | Male   |
| 216     | Daudi   | 324     | Dubbu  | Male   |
| 236     | Hashim  | 311     | Foo    | Male   |
| 216     | Daudi   | 236     | Hashim | Male   |
| 236     | Hashim  | 324     | Dubbu  | Male   |
| 216     | Daudi   | 311     | Foo    | Male   |
+---------+---------+---------+--------+--------+
12 rows in set (0.00 sec)

mysql> select F.StaffNo,F.LName,S.StaffNo,S.LName,F.Position from Staff F,Staff
S where F.Position=S.Position AND F.Staffno<S.Staffno ORDER By F.Staffno,S.Staff
No;
+---------+--------+---------+---------+------------+
| StaffNo | LName  | StaffNo | LName   | Position   |
+---------+--------+---------+---------+------------+
| 102     | Emlia  | 254     | Khan    | Manager    |
| 102     | Emlia  | 324     | Ibrahim | Manager    |
| 145     | Bandar | 235     | Harun   | Assistant  |
| 216     | Fayadi | 236     | Kamal   | Supervisor |
| 216     | Fayadi | 311     | Fatt    | Supervisor |
| 236     | Kamal  | 311     | Fatt    | Supervisor |
| 254     | Khan   | 324     | Ibrahim | Manager    |
+---------+--------+---------+---------+------------+
7 rows in set (0.00 sec)

mysql> select count(Staffno),sum(Salary) from Staff;
+----------------+-------------+
| count(Staffno) | sum(Salary) |
+----------------+-------------+
|              8 |    15350.00 |
+----------------+-------------+
1 row in set (0.00 sec)