Discussion code as seen in video:
C:\xampp\mysql\bin>mysql -u root
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 486
Server version: 10.4.24-MariaDB mariadb.org binary distribution
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> use dbcontacts;
Database changed
MariaDB [dbcontacts]> show tables;
+----------------------+
| Tables_in_dbcontacts |
+----------------------+
| tblcontacts |
| tblgroups |
| tblusers |
+----------------------+
3 rows in set (0.003 sec)
MariaDB [dbcontacts]> dessc tblusers;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'dessc tblusers' at line 1
MariaDB [dbcontacts]> desc tblusers;
+--------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+----------------+
| usr_id | int(11) | NO | PRI | NULL | auto_increment |
| usr_username | varchar(30) | NO | UNI | NULL | |
| usr_password | varchar(30) | NO | | NULL | |
| usr_fullname | varchar(60) | NO | | NULL | |
+--------------+-------------+------+-----+---------+----------------+
4 rows in set (0.023 sec)
MariaDB [dbcontacts]> select * from tblusers;
+--------+--------------+--------------+-------------------+
| usr_id | usr_username | usr_password | usr_fullname |
+--------+--------------+--------------+-------------------+
| 1 | pitok | 12345 | Pitok Batolata |
| 2 | kulas | 54321 | Kulas D. Malas |
| 3 | binimae | 12345 | Princess Bini Mae |
| 30 | kitty | 12345 | Kitty Duterte |
| 31 | cat | 54321 | Cathy Mac |
| 32 | hans | 123 | Hans Ford |
| 33 | tokpi | tokpi | Tokpi B. |
| 34 | mickey | mouse | Mickey Mouse |
| 35 | willie | 12345 | Free Willie |
| 36 | 123 | 123 | 123 123 |
| 37 | elsa | 12345 | elsa frozen |
| 39 | elsa01 | 12345 | elsa frozen |
| 43 | alexp | 12345 | Alex Pereira |
| 45 | james | 12345 | James Magsalay |
| 46 | ashlee | 12345 | Ashlee Tabz |
| 47 | mar | 123 | mar mar |
| 48 | shan | 123 | Shan |
+--------+--------------+--------------+-------------------+
17 rows in set (0.008 sec)
MariaDB [dbcontacts]> desc tblgroups;
+----------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+----------------+
| grp_id | int(11) | NO | PRI | NULL | auto_increment |
| grp_name | varchar(100) | NO | | NULL | |
+----------+--------------+------+-----+---------+----------------+
2 rows in set (0.029 sec)
MariaDB [dbcontacts]> select * from tblgroups;
+--------+------------+
| grp_id | grp_name |
+--------+------------+
| 1 | Family |
| 2 | Friends |
| 3 | Classmates |
| 4 | Colleagues |
| 5 | Enemies |
| 6 | Crushes |
+--------+------------+
6 rows in set (0.006 sec)
MariaDB [dbcontacts]> desc tblcontacts;
+-----------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+--------------+------+-----+---------+----------------+
| contact_id | int(11) | NO | PRI | NULL | auto_increment |
| contact_userId | int(11) | NO | MUL | NULL | |
| contact_name | varchar(100) | NO | | NULL | |
| contact_phone | varchar(15) | NO | | NULL | |
| contact_email | varchar(100) | NO | | NULL | |
| contact_address | varchar(100) | NO | | NULL | |
| contact_group | int(11) | NO | MUL | NULL | |
| contact_image | varchar(50) | NO | | NULL | |
+-----------------+--------------+------+-----+---------+----------------+
8 rows in set (0.027 sec)
MariaDB [dbcontacts]> select * from tblcontacts;
+------------+----------------+--------------------------+---------------+-----------------------+---------------------+---------------+---------------+
| contact_id | contact_userId | contact_name | contact_phone | contact_email | contact_address | contact_group | contact_image |
+------------+----------------+--------------------------+---------------+-----------------------+---------------------+---------------+---------------+
| 1 | 1 | Steve Jobs | 0987876546 | steve@gmail.com | Malaybalay City |
1 | 1.png |
| 3 | 1 | Bongbong Marcos Sr. | 09198765654 | bbm@gmail.com | Taga Cagayan |
3 | 3.jpg |
| 4 | 1 | Sara Duterte Jr. | 09657876453 | saraduterte@yahoo.com | Davao City |
3 | |
| 14 | 2 | Kulasa D. Makakiyomaluya | 666-666-666 | kulasa@gmail.com | Simaya |
3 | |
| 18 | 3 | Pablo Penduko | 1234567890 | pedro@gmail.com | BUkidnon |
6 | |
| 19 | 2 | KULASA DIMAGIBA | 444555666777 | awawa666@gmail.com | Malaybalay City |
6 | |
| 24 | 1 | Arnel Robeniol | 12345354 | bolinao@gmail.com | Piurok 4, Simays |
1 | |
| 26 | 1 | Rycelle Puruel Sr. | 12345 66666 | rycellep@gmail.com | Cagayan de Oro City |
6 | |
+------------+----------------+--------------------------+---------------+-----------------------+---------------------+---------------+---------------+
8 rows in set (0.004 sec)
MariaDB [dbcontacts]> select contact_userId, contact_name, contact_phone
-> from tblcontacts;
+----------------+--------------------------+---------------+
| contact_userId | contact_name | contact_phone |
+----------------+--------------------------+---------------+
| 1 | Steve Jobs | 0987876546 |
| 1 | Bongbong Marcos Sr. | 09198765654 |
| 1 | Sara Duterte Jr. | 09657876453 |
| 2 | Kulasa D. Makakiyomaluya | 666-666-666 |
| 3 | Pablo Penduko | 1234567890 |
| 2 | KULASA DIMAGIBA | 444555666777 |
| 1 | Arnel Robeniol | 12345354 |
| 1 | Rycelle Puruel Sr. | 12345 66666 |
+----------------+--------------------------+---------------+
8 rows in set (0.003 sec)
MariaDB [dbcontacts]> SELECT a.usr_fullname, b.contact_name, b.contact_phone
-> FROM tblusers a INNER JOIN tblcontacts b
-> ON a.usr_id = b.contact_userId;
+-------------------+--------------------------+---------------+
| usr_fullname | contact_name | contact_phone |
+-------------------+--------------------------+---------------+
| Pitok Batolata | Steve Jobs | 0987876546 |
| Pitok Batolata | Bongbong Marcos Sr. | 09198765654 |
| Pitok Batolata | Sara Duterte Jr. | 09657876453 |
| Kulas D. Malas | Kulasa D. Makakiyomaluya | 666-666-666 |
| Princess Bini Mae | Pablo Penduko | 1234567890 |
| Kulas D. Malas | KULASA DIMAGIBA | 444555666777 |
| Pitok Batolata | Arnel Robeniol | 12345354 |
| Pitok Batolata | Rycelle Puruel Sr. | 12345 66666 |
+-------------------+--------------------------+---------------+
8 rows in set (0.001 sec)
MariaDB [dbcontacts]> select contact_name, contact_phone, contact_group from tblcontacts;
+--------------------------+---------------+---------------+
| contact_name | contact_phone | contact_group |
+--------------------------+---------------+---------------+
| Steve Jobs | 0987876546 | 1 |
| Bongbong Marcos Sr. | 09198765654 | 3 |
| Sara Duterte Jr. | 09657876453 | 3 |
| Kulasa D. Makakiyomaluya | 666-666-666 | 3 |
| Pablo Penduko | 1234567890 | 6 |
| KULASA DIMAGIBA | 444555666777 | 6 |
| Arnel Robeniol | 12345354 | 1 |
| Rycelle Puruel Sr. | 12345 66666 | 6 |
+--------------------------+---------------+---------------+
8 rows in set (0.003 sec)
MariaDB [dbcontacts]> SELECT a.grp_name, b.contact_name, b.contact_phone
-> FROM tblgroups a INNER JOIN tblcontacts b
-> ON a.grp_id = b.contact_group;
+------------+--------------------------+---------------+
| grp_name | contact_name | contact_phone |
+------------+--------------------------+---------------+
| Family | Steve Jobs | 0987876546 |
| Classmates | Bongbong Marcos Sr. | 09198765654 |
| Classmates | Sara Duterte Jr. | 09657876453 |
| Classmates | Kulasa D. Makakiyomaluya | 666-666-666 |
| Crushes | Pablo Penduko | 1234567890 |
| Crushes | KULASA DIMAGIBA | 444555666777 |
| Family | Arnel Robeniol | 12345354 |
| Crushes | Rycelle Puruel Sr. | 12345 66666 |
+------------+--------------------------+---------------+
8 rows in set (0.002 sec)
MariaDB [dbcontacts]> SELECT a.usr_fullname, c.contact_name, c.contact_phone, b.grp_name
-> FROM tblusers a INNER JOIN tblcontacts c ON a.usr_id = c.contact_userId
-> INNER JOIN tblgroups b ON b.grp_id = c.contact_group;
+-------------------+--------------------------+---------------+------------+
| usr_fullname | contact_name | contact_phone | grp_name |
+-------------------+--------------------------+---------------+------------+
| Pitok Batolata | Steve Jobs | 0987876546 | Family |
| Pitok Batolata | Bongbong Marcos Sr. | 09198765654 | Classmates |
| Pitok Batolata | Sara Duterte Jr. | 09657876453 | Classmates |
| Kulas D. Malas | Kulasa D. Makakiyomaluya | 666-666-666 | Classmates |
| Princess Bini Mae | Pablo Penduko | 1234567890 | Crushes |
| Kulas D. Malas | KULASA DIMAGIBA | 444555666777 | Crushes |
| Pitok Batolata | Arnel Robeniol | 12345354 | Family |
| Pitok Batolata | Rycelle Puruel Sr. | 12345 66666 | Crushes |
+-------------------+--------------------------+---------------+------------+
8 rows in set (0.003 sec)
MariaDB [dbcontacts]> use ebexam;
ERROR 1049 (42000): Unknown database 'ebexam'
MariaDB [dbcontacts]> use dbexam;
Database changed
MariaDB [dbexam]> show tables;
+------------------+
| Tables_in_dbexam |
+------------------+
| tblclassdensity |
| tblcourses |
| tblelection |
| tblgender |
| tblgrades |
| tblpetowners |
| tblpets |
| tblpettypes |
| tblstudents |
+------------------+
9 rows in set (0.003 sec)
MariaDB [dbexam]> select a.owner_name, b.pet_name
-> FROM tblpetowners a INNER JOIN tblpets b ON a.owner_id=b.pet_owner
-> ORDER BY a.owner_name;
+----------------+----------+
| owner_name | pet_name |
+----------------+----------+
| KARDING BAGUIO | BINI MAE |
| KARDING BAGUIO | RALPH |
| KULAS D. MALAS | MAKYUZ |
| KULAS D. MALAS | RAZZ |
| PITOK BATOLATA | GRAYZONE |
| PITOK BATOLATA | MARCHAMZ |
+----------------+----------+
6 rows in set (0.003 sec)
MariaDB [dbexam]> SELECT b.pet_name, a.ptype_name
-> FROM tblpettype a INNER JOIN tblpets b ON a.ptype_id = b.pet_type
-> ORDER BY a.pet_name;
ERROR 1146 (42S02): Table 'dbexam.tblpettype' doesn't exist
MariaDB [dbexam]> SELECT b.pet_name, a.ptype_name
-> FROM tblpettypes a INNER JOIN tblpets b ON a.ptype_id = b.pet_type
-> ORDER BY a.pet_name;
ERROR 1054 (42S22): Unknown column 'a.pet_name' in 'order clause'
MariaDB [dbexam]> SELECT b.pet_name, a.ptype_name
-> FROM tblpettype a INNER JOIN tblpets b ON a.ptype_id = b.pet_type
-> ORDER BY b.pet_name;
ERROR 1146 (42S02): Table 'dbexam.tblpettype' doesn't exist
MariaDB [dbexam]> SELECT b.pet_name, a.ptype_name
-> FROM tblpettypes a INNER JOIN tblpets b ON a.ptype_id = b.pet_type
-> ORDER BY b.pet_name;
+----------+------------+
| pet_name | ptype_name |
+----------+------------+
| BINI MAE | SIGBIN |
| GRAYZONE | DOG |
| MAKYUZ | CAT |
| MARCHAMZ | DOG |
| RALPH | SIGBIN |
| RAZZ | CAT |
+----------+------------+
6 rows in set (0.003 sec)
MariaDB [dbexam]> SELECT b.pet_name as 'NAME OF PET', a.ptype_name as 'TYPE OF PET'
-> FROM tblpettypes a INNER JOIN tblpets b ON a.ptype_id = b.pet_type
-> ORDER BY b.pet_name;
+-------------+-------------+
| NAME OF PET | TYPE OF PET |
+-------------+-------------+
| BINI MAE | SIGBIN |
| GRAYZONE | DOG |
| MAKYUZ | CAT |
| MARCHAMZ | DOG |
| RALPH | SIGBIN |
| RAZZ | CAT |
+-------------+-------------+
6 rows in set (0.002 sec)
MariaDB [dbexam]> SELECT a.owner_name as 'NAME OF OWNER', c.pet_name as 'PET NAME', b.ptype_name as 'TYPE OF PET'
-> FROM tblpetowners a INNER JOIN tblpets c ON a.owner_id=c.pet_owner
-> INNER JOIN tblpettypes b INNER JOIN b.ptype_id=c.pet_type
-> ORDER BY a.owner_name, c.pet_name;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '.pet_type
ORDER BY a.owner_name, c.pet_name' at line 3
MariaDB [dbexam]> SELECT a.owner_name as 'NAME OF OWNER', c.pet_name as 'PET NAME', b.ptype_name as 'TYPE OF PET'
-> FROM tblpetowners a INNER JOIN tblpets c ON a.owner_id=c.pet_owner
-> INNER JOIN tblpettypes b INNER JOIN tblpets b.ptype_id=c.pet_type
-> ORDER BY a.owner_name, c.pet_name;
ERROR 1066 (42000): Not unique table/alias: 'b'
MariaDB [dbexam]> SELECT a.owner_name as 'NAME OF OWNER', c.pet_name as 'PET NAME', b.ptype_name as 'TYPE OF PET'
-> FROM tblpetowners a INNER JOIN tblpets c ON a.owner_id=c.pet_owner
-> INNER JOIN tblpettypes b ON b.ptype_id=c.pet_type
-> ORDER BY a.owner_name, c.pet_name;
+----------------+----------+-------------+
| NAME OF OWNER | PET NAME | TYPE OF PET |
+----------------+----------+-------------+
| KARDING BAGUIO | BINI MAE | SIGBIN |
| KARDING BAGUIO | RALPH | SIGBIN |
| KULAS D. MALAS | MAKYUZ | CAT |
| KULAS D. MALAS | RAZZ | CAT |
| PITOK BATOLATA | GRAYZONE | DOG |
| PITOK BATOLATA | MARCHAMZ | DOG |
+----------------+----------+-------------+
6 rows in set (0.011 sec)
MariaDB [dbexam]> create table tblambot(a_name varchar(100));
Query OK, 0 rows affected (0.038 sec)
MariaDB [dbexam]>
No comments:
Post a Comment