[go: up one dir, main page]
Scribd
Upload
8 views11 pages

CS

The document outlines the creation of multiple databases for employee, vehicle, marketing, and site management, including the structure of various tables within each database. It provides SQL commands to create tables for employee details, salary records, vehicle data, profit tracking, and site information. Additionally, it includes a Python program to connect to a MySQL database and display available databases.

Uploaded by

gsureshkrishna
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 views11 pages

CS

The document outlines the creation of multiple databases for employee, vehicle, marketing, and site management, including the structure of various tables within each database. It provides SQL commands to create tables for employee details, salary records, vehicle data, profit tracking, and site information. Additionally, it includes a Python program to connect to a MySQL database and display available databases.

Uploaded by

gsureshkrishna
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/ 11

HYPE EARTHMOVERS

EMPLOYEE_MANAGEMENT
1.create database emp_mng;
2. use emp_mng;
3. create table employee(emp_id varchar(5),emp_name
char(20),age varchar(2),doj date,profession
char(20),phone varchar(10));
4. create table salary_basis(s_basis char(2),salary
varchar(6));
5. create table salary_year(emp_id varchar(5),s_basis
char(2),apr varchar(6),may varchar(6),jun varchar(6),jul
varchar(6),aug varchar(6),sep varchar(6),oct
varchar(6),nov varchar(6),decem varchar(6),jan
varchar(6),feb varchar(6),mar varchar(6));
6. create table work_on_site(emp_id varchar(5),site_no
varchar(10),site_date date);
7. create table advance(emp_id varchar(5),adv_date
date,amt varchar(6),remaining_sal varchar(6));
8.desc employee;
+------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| emp_id | varchar(5) | YES | | NULL | |
| emp_name | char(20) | YES | | NULL | |
| age | varchar(2) | YES | | NULL | |
| doj | date | YES | | NULL | |
| profession | char(20) | YES | | NULL | |
| phone | varchar(10) | YES | | NULL | |
+------------+-------------+------+-----+---------+-------+
9. desc salary_basis;
+---------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+------------+------+-----+---------+-------+
| s_basis | char(2) | YES | | NULL | |
| salary | varchar(6) | YES | | NULL | |
+---------+------------+------+-----+---------+-------+
10. desc salary_year;
+---------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+------------+------+-----+---------+-------+
| emp_id | varchar(5) | YES | | NULL | |
| s_basis | char(2) | YES | | NULL | |
| apr | varchar(6) | YES | | NULL | |
| may | varchar(6) | YES | | NULL | |
| jun | varchar(6) | YES | | NULL | |
| jul | varchar(6) | YES | | NULL | |
| aug | varchar(6) | YES | | NULL | |
| sep | varchar(6) | YES | | NULL | |
| oct | varchar(6) | YES | | NULL | |
| nov | varchar(6) | YES | | NULL | |
| decem | varchar(6) | YES | | NULL | |
| jan | varchar(6) | YES | | NULL | |
| feb | varchar(6) | YES | | NULL | |
| mar | varchar(6) | YES | | NULL | |
+---------+------------+------+-----+---------+-------+
11. desc work_on_site;
+-----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| emp_id | varchar(5) | YES | | NULL | |
| site_no | varchar(10) | YES | | NULL | |
| site_date | date | YES | | NULL | |
+-----------+-------------+------+-----+---------+-------+
12. desc advance;
+---------------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------+------------+------+-----+---------+-------+
| emp_id | varchar(5) | YES | | NULL | |
| adv_date | date | YES | | NULL | |
| amt | varchar(6) | YES | | NULL | |
| remaining_sal | varchar(6) | YES | | NULL | |
VECHILE _MANAGEMENT

13. create database vehicle_mng;


14. use vehicle_mng;
15. create table vehicle_data(v_id varchar(15),v_type
char(20));
16. create table work_on_site(v_id varchar(15),site_date
date,site_no varchar(10));
17. create table maintanence(v_id varchar(15),m_date
date,service_type char(20),amt varchar(6));
18. desc vehicle_data;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| v_id | varchar(15) | YES | | NULL | |
| v_type | char(20) | YES | | NULL | |
+--------+-------------+------+-----+---------+-------+
19. desc work_on_site;
+-----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| v_id | varchar(15) | YES | | NULL | |
| site_date | date | YES | | NULL | |
| site_no | varchar(10) | YES | | NULL | |
+-----------+-------------+------+-----+---------+-------+
20. desc maintanence;
+--------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| v_id | varchar(15) | YES | | NULL | |
| m_date | date | YES | | NULL | |
| service_type | char(20) | YES | | NULL | |
| amt | varchar(6) | YES | | NULL | |
MARKETING _MANAGEMENT
21. create database marketing_mng;
22. use marketing_mng;
↨23. create table profit(site_no varchar(10),amt
varchar(7),salary varchar(6),profit_or_loss varchar(6));
24. create table profit_total(month
char(10),total_profit_or_loss varchar(6));
25. create table borrowings(b_date date,borrower_name
char(20),amt varchar(6),repayment varchar(6));
26. desc profit;
+----------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+-------------+------+-----+---------+-------+
| site_no | varchar(10) | YES | | NULL | |
| amt | varchar(7) | YES | | NULL | |
| salary | varchar(6) | YES | | NULL | |
| profit_or_loss | varchar(6) | YES | | NULL | |
+----------------+-------------+------+-----+---------+-------+
27. desc profit_total;
+----------------------+------------+------+-----+---------
+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------------+------------+------+-----+---------
+-------+
| month | char(10) | YES | | NULL | |
| total_profit_or_loss | varchar(6) | YES | | NULL |
|
+----------------------+------------+------+-----+---------+------
28. desc borrowings;
+---------------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------+------------+------+-----+---------+-------+
| b_date | date | YES | | NULL | |
| borrower_name | char(20) | YES | | NULL | |
| amt | varchar(6) | YES | | NULL | |
| repayment | varchar(6) | YES | | NULL | |
+---------------+------------+------+-----+---------+-------+
SITES_MANAGEMENT
29. create database sites_mng;
30. use sites_mng;
31. create table site_data (site_no varchar(10),site_name
char(20),no_of_vehicle char(5),no_of_emp
varchar(3),amt varchar(6));
32. desc site_data;
+---------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------+-------------+------+-----+---------+-------+
| site_no | varchar(10) | YES | | NULL | |
| site_name | char(20) | YES | | NULL | |
| no_of_vehicle | char(5) | YES | | NULL | |
| no_of_emp | varchar(3) | YES | | NULL | |
| amt | varchar(6) | YES | | NULL | |
+---------------+-------------+------+-----+---------+-------+
PROGRAM:
import mysql.connector as ms
co=ms.connect(host="localhost",user="root",passwd="rootroot")
if co.is_connected:
print("connection successful")
cur=co.cursor()
cur.execute("Show databases")
print(" ******** Available databases are
******** ")
a=cur.fetchall()
b=0
for i in range(1,len(a)+1):
print(i,".",end=" ")
for i in a[b]:
print(i)
b=b+1

You might also like