සමන් බණ්ඩාර හේරත්ගේ සටහන් පොත: MySQL භාවිත කර දත්ත සමුදායක් සකසමු (උසස් පෙළ තොරතුරු හා සන්නිවේදන තාක්ෂණ සිසුන් සඳහා අභ්‍යාසයක්)

පාසලක සිසුන්ගේ සහ ඔවුන් හදාරන විෂයයන් පිළිබදවත් එසේම ඔවුන් සහභාගී වන ක්‍රීඩාවන් පිළිබඳවත් තොරතුරු ඒකරාශිකිරීම සඳහා MySQL භාවිත කරමින් සකස් කරන ලද දත්ත සමුදායක Syntax පෙළ පහතින් ඉදිරිපත් කර ඇත.

නිර්මාණය කළ සිසුවියට දිරි ගැන්වීමක් ලෙසත් අන් අයට උත්සාහ කිරීමට අවස්ථාවක් ලබා දීමක් වශයෙනුත් මෙය ඉදිරිපත් කරන්නට සිතුවෙමි.

mysql> CREATE DATABASE St_Joseph_Details;
Query OK, 1 row affected (0.03 sec)

mysql> USE St_Joseph_Details;
Database changed
mysql> CREATE TABLE Student(St_Ad_No VARCHAR(10) NOT NULL,St_Name VARCHAR(50) NOT NULL,St_Address VARCHAR(50) NOT NULL,St_DOB VARCHAR(10) NOT NULL,St_Phone_NO VARCHAR(10) NOT NULL);
Query OK, 0 rows affected (0.53 sec)

mysql> CREATE TABLE Sport(Sp_ ID VARCHAR(10) NOT NULL,Sp_Name VARCHAR(25) NOT NULL,Sp_Coach VARCHAR(50) NOT NULL);
Query OK, 0 rows affected (0.11 sec)

mysql> CREATE TABLE Participate(hours VARCHAR(20) NOT NULL,St_Ad_No VARCHAR(10) NOT NULL,Sp_ID VARCHAR(10) NOT NULL);
Query OK, 0 rows affected (0.39 sec)

mysql> ALTER TABLE Student ADD PRIMARY KEY(St_Ad_No);
Query OK, 0 rows affected (0.48 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> ALTER TABLE Sport ADD PRIMARY KEY(Sp_ID);
Query OK, 0 rows affected (0.55 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> ALTER TABLE Participate ADD PRIMARY KEY(St_Ad_No,Sp_ID);
Query OK, 0 rows affected (0.50 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> ALTER TABLE Participate ADD FOREIGN KEY(St_Ad_No) REFERENCES Student(St_Ad_No);
Query OK, 0 rows affected (0.53 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> ALTER TABLE Participate ADD FOREIGN KEY(Sp_ID) REFERENCES Sport(Sp_ID);
Query OK, 0 rows affected (0.44 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> CREATE TABLE Subject(Sub_ID VARCHAR(10) NOT NULL,Sub_Name VARCHAR(50) NOT NULL,Sub_Teacher VARCHAR(50) NOT NULL);
Query OK, 0 rows affected (0.44 sec)

mysql> CREATE TABLE Learn(Sub_Monitor VARCHAR(10) NOT NULL,St_ Ad_No VARCHAR(10) NOT NULL,Sub_ID VARCHAR(10) NOT NULL);
Query OK, 0 rows affected (0.41 sec)

mysql> ALTER TABLE Subject ADD PRIMARY KEY(Sub_ID);
Query OK, 0 rows affected (0.45 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> ALTER TABLE Learn ADD PRIMARY KEY(St_Ad_no,Sub_ ID);
Query OK, 0 rows affected (0.41 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> ALTER TABLE Learn ADD FOREIGN KEY(Sub_ID) REFERENCES Subject(Sub_ID);
Query OK, 0 rows affected (0.44 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> ALTER TABLE Learn ADD FOREIGN KEY(St_ Ad_No) REFERENCES Student(St_Ad_No);
Query OK, 0 rows affected (0.44 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> SHOW CREATE TABLE Student;
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Student | CREATE TABLE `student` (
`St_Ad_No` varchar(10) NOT NULL,
`St_Name` varchar(50) NOT NULL,
`St_Address` varchar(50) NOT NULL,
`St_DOB` varchar(10) NOT NULL,
`St_Phone_NO` varchar(10) NOT NULL,
PRIMARY KEY (`St_Ad_No`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SHOW CREATE TABLE Sport;
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Sport | CREATE TABLE `sport` (
`Sp_ID` varchar(10) NOT NULL,
`Sp_Name` varchar(25) NOT NULL,
`Sp_Coach` varchar(50) NOT NULL,
PRIMARY KEY (`Sp_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SHOW CREATE TABLE Participate;
+-------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Participate | CREATE TABLE `participate` (
`hours` varchar(20) NOT NULL,
`St_Ad_No` varchar(10) NOT NULL,
`Sp_ID` varchar(10) NOT NULL,
PRIMARY KEY (`St_Ad_No`,`Sp_ID`),
KEY `Sp_ID` (`Sp_ID`),
CONSTRAINT `participate_ibfk_2` FOREIGN KEY (`Sp_ID`) REFERENCES `sport` (`Sp_ID`),
CONSTRAINT `participate_ibfk_1` FOREIGN KEY (`St_Ad_No`) REFERENCES `student` (`St_Ad_No`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SHOW CREATE TABLE Subject;
+---------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+---------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Subject | CREATE TABLE `subject` (
`Sub_ID` varchar(10) NOT NULL,
`Sub_Name` varchar(50) NOT NULL,
`Sub_Teacher` varchar(50) NOT NULL,
PRIMARY KEY (`Sub_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+---------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SHOW CREATE TABLE Learn;
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Learn | CREATE TABLE `learn` (
`Sub_Monitor` varchar(10) NOT NULL,
`St_Ad_No` varchar(10) NOT NULL,
`Sub_ID` varchar(10) NOT NULL,
PRIMARY KEY (`St_Ad_No`,`Sub_ID`),
KEY `Sub_ID` (`Sub_ID`),
CONSTRAINT `learn_ibfk_2` FOREIGN KEY (`St_Ad_No`) REFERENCES `student` (`St_Ad_No`),
CONSTRAINT `learn_ibfk_1` FOREIGN KEY (`Sub_ID`) REFERENCES `subject` (`Sub_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> INSERT INTO Student SET St_Ad_No='1000',St_Name='Hashini',St_Address='Alupotha,Ussapitiya',St_DOB='24/11/1997',St_Phone_No='0352258511';
Query OK, 1 row affected (0.39 sec)

mysql> INSERT INTO Student SET St_Ad_No='1001',St_Name='Dinushika',St_Address='Kegalle',St_DOB='22/2/1997',St_Phone_No='0353351873';
Query OK, 1 row affected (0.36 sec)

mysql> INSERT INTO Student SET St_Ad_No='1002',St_Name='Thiyangi',St_Address='Balapattawa,Galigamuwa',St_DOB='27/6/1997',St_Phone_No='0352345162';
Query OK, 1 row affected (0.39 sec)

mysql> INSERT INTO Student SET St_Ad_No='1003',St_Name='Theja',St_Address='Ranwala,Kegalle',St_DOB='5/10/1997',St_Phone_No='0356494532';
Query OK, 1 row affected (0.38 sec)

mysql> INSERT INTO Sport SET Sp_ID='12345',Sp_Name='Chess',Sp_Coach='Sir.Dulan';
Query OK, 1 row affected (0.36 sec)

mysql> INSERT INTO Sport SET Sp_ID='23456',Sp_Name='Badminton',Sp_Coach='Sir.Perera';
Query OK, 1 row affected (0.34 sec)

mysql> INSERT INTO Sport SET Sp_ID='34567',Sp_Name='Carrom',Sp_Coach='Sir.Silva';
Query OK, 1 row affected (0.38 sec)

mysql> INSERT INTO Participate SET hours='3 hours',St_Ad_No='1000',Sp_ID='12345';
Query OK, 1 row affected (0.09 sec)

mysql> INSERT INTO Participate SET hours='3 hours',St_Ad_No='1001',Sp_ID='12345';
Query OK, 1 row affected (0.09 sec)

mysql> INSERT INTO Participate SET hours='3 hours',St_Ad_No='1001',Sp_ID='23456';
Query OK, 1 row affected (0.09 sec)

mysql> INSERT INTO Participate SET hours='3 hours',St_Ad_No='1002',Sp_ID='23456';
Query OK, 1 row affected (0.09 sec)

mysql> INSERT INTO Participate SET hours='2 hours',St_Ad_No='1003',Sp_ID='34567';
Query OK, 1 row affected (0.09 sec)

mysql> INSERT INTO Subject SET Sub_ID='SI123',Sub_Name='Maths',Sub_Teacher='Mrs.Chandra';
Query OK, 1 row affected (0.11 sec)

mysql> INSERT INTO Subject SET Sub_ID='SI234',Sub_Name='ICT',Sub_Teacher='Miss.Maheshi';
Query OK, 1 row affected (0.11 sec)

mysql> INSERT INTO Subject SET Sub_ID='SI345',Sub_Name='Physics',Sub_Teacher='Mr.Gamini';
Query OK, 1 row affected (0.09 sec)

mysql> INSERT INTO Learn SET Sub_Monitor='Yes',St_Ad_No='1000',Sub_ID='SI123';
Query OK, 1 row affected (0.36 sec)

mysql>
mysql> INSERT INTO Learn SET Sub_Monitor='No',St_Ad_No='1001',Sub_ID='SI123';
Query OK, 1 row affected (0.08 sec)

mysql> INSERT INTO Learn SET Sub_Monitor='Yes',St_Ad_No='1001',Sub_ID='SI234';
Query OK, 1 row affected (0.47 sec)

mysql> SELECT * FROM Subject;
+--------+----------+--------------+
| Sub_ID | Sub_Name | Sub_Teacher |
+--------+----------+--------------+
| SI123 | Maths | Mrs.Chandra |
| SI234 | ICT | Miss.Maheshi |
| SI345 | Physics | Mr.Gamini |
+--------+----------+--------------+
3 rows in set (0.00 sec)

mysql> INSERT INTO Learn SET Sub_Monitor='Yes',St_Ad_No='1002',Sub_ID='SI345';
Query OK, 1 row affected (0.58 sec)

mysql> INSERT INTO Learn SET Sub_Monitor='No',St_Ad_No='1003',Sub_ID='SI345';
Query OK, 1 row affected (0.36 sec)

mysql> SELECT * FROM Student;
+----------+-----------+------------------------+------------+-------------+
| St_Ad_No | St_Name | St_Address | St_DOB | St_Phone_NO |
+----------+-----------+------------------------+------------+-------------+
| 1000 | Hashini | Alupotha,Ussapitiya | 24/11/1997 | 0352258511 |
| 1001 | Dinushika | Kegalle | 22/2/1997 | 0353351873 |
| 1002 | Thiyangi | Balapattawa,Galigamuwa | 27/6/1997 | 0352345162 |
| 1003 | Theja | Ranwala,Kegalle | 5/10/1997 | 0356494532 |
+----------+-----------+------------------------+------------+-------------+
4 rows in set (0.00 sec)

mysql> SELECT * FROM Sport;
+-------+-----------+------------+
| Sp_ID | Sp_Name | Sp_Coach |
+-------+-----------+------------+
| 12345 | Chess | Sir.Dulan |
| 23456 | Badminton | Sir.Perera |
| 34567 | Carrom | Sir.Silva |
+-------+-----------+------------+
3 rows in set (0.00 sec)

mysql> SELECT * FROM Participate;
+---------+----------+-------+
| hours | St_Ad_No | Sp_ID |
+---------+----------+-------+
| 3 hours | 1000 | 12345 |
| 3 hours | 1001 | 12345 |
| 3 hours | 1001 | 23456 |
| 3 hours | 1002 | 23456 |
| 2 hours | 1003 | 34567 |
+---------+----------+-------+
5 rows in set (0.00 sec)

mysql> SELECT * FROM Subject;
+--------+----------+--------------+
| Sub_ID | Sub_Name | Sub_Teacher |
+--------+----------+--------------+
| SI123 | Maths | Mrs.Chandra |
| SI234 | ICT | Miss.Maheshi |
| SI345 | Physics | Mr.Gamini |
+--------+----------+--------------+
3 rows in set (0.00 sec)

mysql> SELECT * FROM Learn;
+-------------+----------+--------+
| Sub_Monitor | St_Ad_No | Sub_ID |
+-------------+----------+--------+
| Yes | 1000 | SI123 |
| No | 1001 | SI123 |
| Yes | 1001 | SI234 |
| Yes | 1002 | SI345 |
| No | 1003 | SI345 |
+-------------+----------+--------+
5 rows in set (0.00 sec)

mysql> SELECT St_Ad_No,St_Address FROM Student;
+----------+------------------------+
| St_Ad_No | St_Address |
+----------+------------------------+
| 1000 | Alupotha,Ussapitiya |
| 1001 | Kegalle |
| 1002 | Balapattawa,Galigamuwa |
| 1003 | Ranwala,Kegalle |
+----------+------------------------+
4 rows in set (0.00 sec)

mysql> SELECT * FROM Student WHERE St_Name='Hashini';
+----------+---------+---------------------+------------+-------------+
| St_Ad_No | St_Name | St_Address | St_DOB | St_Phone_NO |
+----------+---------+---------------------+------------+-------------+
| 1000 | Hashini | Alupotha,Ussapitiya | 24/11/1997 | 0352258511 |
+----------+---------+---------------------+------------+-------------+
1 row in set (0.34 sec)

mysql> SELECT * FROM Subject WHERE Sub_ID;
Empty set, 3 warnings (0.00 sec)

mysql> SELECT * FROM Subject WHERE Sub_ID='SI123';
+--------+----------+-------------+
| Sub_ID | Sub_Name | Sub_Teacher |
+--------+----------+-------------+
| SI123 | Maths | Mrs.Chandra |
+--------+----------+-------------+
1 row in set (0.00 sec)

mysql> SELECT St_Ad_No,St_Name,St_Address FROM Student;
+----------+-----------+------------------------+
| St_Ad_No | St_Name | St_Address |
+----------+-----------+------------------------+
| 1000 | Hashini | Alupotha,Ussapitiya |
| 1001 | Dinushika | Kegalle |
| 1002 | Thiyangi | Balapattawa,Galigamuwa |
| 1003 | Theja | Ranwala,Kegalle |
+----------+-----------+------------------------+
4 rows in set (0.00 sec)

mysql> SELECT St_Ad_No,St_Name,St_Address FROM Student WHERE St_Ad_No='1003';
+----------+---------+-----------------+
| St_Ad_No | St_Name | St_Address |
+----------+---------+-----------------+
| 1003 | Theja | Ranwala,Kegalle |
+----------+---------+-----------------+
1 row in set (0.00 sec)

mysql> SELECT Sub_ID,Sub_Name FROM Subject WHERE Sub_ID='SI123';
+--------+----------+
| Sub_ID | Sub_Name |
+--------+----------+
| SI123 | Maths |
+--------+----------+
1 row in set (0.00 sec)

mysql> SELECT * FROM Student WHERE St_Name='Hashini' AND St_Name='Theja';
Empty set (0.00 sec)

mysql> SELECT * FROM Student WHERE St_Name='Hashini' AND St_Address='Kegalle';
Empty set (0.00 sec)

mysql> INSERT INTO Student SET St_Ad_No='1004',St_Name='Hashini',St_Address='Kegalle',St_Phone_No='0352234561';
Query OK, 1 row affected, 1 warning (0.39 sec)

mysql> SELECT * FROM Student;
+----------+-----------+------------------------+------------+-------------+
| St_Ad_No | St_Name | St_Address | St_DOB | St_Phone_NO |
+----------+-----------+------------------------+------------+-------------+
| 1000 | Hashini | Alupotha,Ussapitiya | 24/11/1997 | 0352258511 |
| 1001 | Dinushika | Kegalle | 22/2/1997 | 0353351873 |
| 1002 | Thiyangi | Balapattawa,Galigamuwa | 27/6/1997 | 0352345162 |
| 1003 | Theja | Ranwala,Kegalle | 5/10/1997 | 0356494532 |
| 1004 | Hashini | Kegalle | | 0352234561 |
+----------+-----------+------------------------+------------+-------------+
5 rows in set (0.00 sec)

mysql> SELECT * FROM Student WHERE St_Name='Hashini' AND St_Address='Kegalle';
+----------+---------+------------+--------+-------------+
| St_Ad_No | St_Name | St_Address | St_DOB | St_Phone_NO |
+----------+---------+------------+--------+-------------+
| 1004 | Hashini | Kegalle | | 0352234561 |
+----------+---------+------------+--------+-------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM Student WHERE St_Name='Hashini' OR St_Address='Kegalle';
+----------+-----------+---------------------+------------+-------------+
| St_Ad_No | St_Name | St_Address | St_DOB | St_Phone_NO |
+----------+-----------+---------------------+------------+-------------+
| 1000 | Hashini | Alupotha,Ussapitiya | 24/11/1997 | 0352258511 |
| 1001 | Dinushika | Kegalle | 22/2/1997 | 0353351873 |
| 1004 | Hashini | Kegalle | | 0352234561 |
+----------+-----------+---------------------+------------+-------------+
3 rows in set (0.00 sec)

mysql> SELECT * FROM Student WHERE St_Name='Hashini' OR St_Address='Kegalle' AND St_Address='Ranwala,Kegalle';
+----------+---------+---------------------+------------+-------------+
| St_Ad_No | St_Name | St_Address | St_DOB | St_Phone_NO |
+----------+---------+---------------------+------------+-------------+
| 1000 | Hashini | Alupotha,Ussapitiya | 24/11/1997 | 0352258511 |
| 1004 | Hashini | Kegalle | | 0352234561 |
+----------+---------+---------------------+------------+-------------+
2 rows in set (0.00 sec)

mysql> UPDATE Student SET St_Name='Hashini Koswattha' WHERE St_Ad_No='1000';
Query OK, 1 row affected (0.39 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> SELECT * FROM Student;
+----------+-------------------+------------------------+------------+-------------+
| St_Ad_No | St_Name | St_Address | St_DOB | St_Phone_NO |
+----------+-------------------+------------------------+------------+-------------+
| 1000 | Hashini Koswattha | Alupotha,Ussapitiya | 24/11/1997 | 0352258511 |
| 1001 | Dinushika | Kegalle | 22/2/1997 | 0353351873 |
| 1002 | Thiyangi | Balapattawa,Galigamuwa | 27/6/1997 | 0352345162 |
| 1003 | Theja | Ranwala,Kegalle | 5/10/1997 | 0356494532 |
| 1004 | Hashini | Kegalle | | 0352234561 |
+----------+-------------------+------------------------+------------+-------------+
5 rows in set (0.00 sec)

mysql> SELECT * FROM Participate;
+---------+----------+-------+
| hours | St_Ad_No | Sp_ID |
+---------+----------+-------+
| 3 hours | 1000 | 12345 |
| 3 hours | 1001 | 12345 |
| 3 hours | 1001 | 23456 |
| 3 hours | 1002 | 23456 |
| 2 hours | 1003 | 34567 |
+---------+----------+-------+
5 rows in set (0.00 sec)

mysql> DELETE FROM Participate WHERE St_Ad_No='1000';
Query OK, 1 row affected (0.38 sec)

MySQL භාවිත කර දත්ත සමුදායක් සකසමු (උසස් පෙළ තොරතුරු හා සන්නිවේදන තාක්ෂණ සිසුන් සඳහා අභ්‍යාසයක්)

No comments: