World's Best AI Learning Platform with profoundly Demanding Certification Programs
Designed by IITians, only for AI Learners.
Designed by IITians, only for AI Learners.
New to InsideAIML? Create an account
Employer? Create an account
Download our e-book of Introduction To Python
4.5 (1,292 Ratings)
559 Learners
Neha Kumawat
a year ago
mysql> CREATE TABLE EMPLOYEE(
FIRST_NAME CHAR(20) NOT NULL,
LAST_NAME CHAR(20),
AGE INT,
SEX CHAR(1),
INCOME FLOAT,
CONTACT INT
);
Query OK, 0 rows affected (0.36 sec)
INSERT INTO Employee VALUES
('Ramya', 'Rama Priya', 27, 'F', 9000, 101),
('Vinay', 'Bhattacharya', 20, 'M', 6000, 102),
('Sharukh', 'Sheik', 25, 'M', 8300, 103),
('Sarmista', 'Sharma', 26, 'F', 10000, 104),
('Trupthi', 'Mishra', 24, 'F', 6000, 105);
Query OK, 5 rows affected (0.08 sec)
Records: 5 Duplicates: 0 Warnings: 0
CREATE TABLE CONTACT(
ID INT NOT NULL,
EMAIL CHAR(20) NOT NULL,
PHONE LONG,
CITY CHAR(20)
);
Query OK, 0 rows affected (0.49 sec)
INSERT INTO CONTACT (ID, EMAIL, CITY) VALUES
(101, 'Krishna@mymail.com', 'Hyderabad'),
(102, 'Raja@mymail.com', 'Vishakhapatnam'),
(103, 'Krishna@mymail.com', 'Pune'),
(104, 'Raja@mymail.com', 'Mumbai');
Query OK, 4 rows affected (0.10 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> SELECT * from EMPLOYEE INNER JOIN CONTACT ON EMPLOYEE.CONTACT = CONTACT.ID;
+------------+--------------+------+------+--------+---------+-----+--------------------+-------+----------------+
| FIRST_NAME | LAST_NAME | AGE | SEX | INCOME | CONTACT | ID | EMAIL | PHONE | CITY |
+------------+--------------+------+------+--------+---------+-----+--------------------+-------+----------------+
| Ramya | Rama Priya | 27 | F | 9000 | 101 | 101 | Krishna@mymail.com | NULL | Hyderabad |
| Vinay | Bhattacharya | 20 | M | 6000 | 102 | 102 | Raja@mymail.com | NULL | Vishakhapatnam |
| Sharukh | Sheik | 25 | M | 8300 | 103 | 103 | Krishna@mymail.com | NULL | Pune |
| Sarmista | Sharma | 26 | F | 10000 | 104 | 104 | Raja@mymail.com | NULL | Mumbai |
+------------+--------------+------+------+--------+---------+-----+--------------------+-------+----------------+
4 rows in set (0.00 sec)
import mysql.connector
#establishing the connection
conn = mysql.connector.connect(
user='root', password='password', host='127.0.0.1', database='mydb'
)
#Creating a cursor object using the cursor() method
cursor = conn.cursor()
#Retrieving single row
sql = '''SELECT * from EMPLOYEE INNER JOIN CONTACT ON EMPLOYEE.CONTACT = CONTACT.ID'''
#Executing the query
cursor.execute(sql)
#Fetching 1st row from the table
result = cursor.fetchall();
print(result)
#Closing the connection
conn.close()
[
('Krishna', 'Sharma', 26, 'M', 2000, 101, 101, 'Krishna@mymail.com', 9848022338, 'Hyderabad'),
('Raj', 'Kandukuri', 20, 'M', 7000, 102, 102, 'Raja@mymail.com', 9848022339, 'Vishakhapatnam'),
('Ramya', 'Ramapriya', 29, 'F', 5000, 103, 103, 'Krishna@mymail.com', 9848022337, 'Pune'),
('Mac', 'Mohan', 26, 'M', 2000, 104, 104, 'Raja@mymail.com', 9848022330, 'Mumbai')
]