SQL Queries

SQL Queries

create database school_data;

use school_data;

create table admins

(first_name varchar(20) Not Null,

middle_initial varchar(1) Not null,

last_name varchar(20) NOt NULL,

DOB varchar(10) NOt NULL,

phone varchar(10) Primary key,

alt_phone varchar(10),

email varchar(150) NOt NULL,

designation varchar(60) NOt NULL,

subjects_taken varchar(150),

classes_taken varchar(20));

create table class_teachers

(first_name varchar(20) Not Null,

middle_initial varchar(1) Not null,

last_name varchar(20) NOt NULL,

DOB varchar(10) NOt NULL,

phone varchar(10) Primary key,

alt_phone varchar(10),

email varchar(150) NOt NULL,

subjects_taken varchar(150),

class_teacher_of varchar(2) Not Null,

classes_taken varchar(20));

create table student_stats

(ADMISSION_NO varchar(15) PRImary key,

ROLL_NO int NOt NULL,

GRADE varchar(10) NOt NULL,

SECTION varchar(1) NOt NULL,

DOB varchar(10) NOt NULL,

FIRST_NAME varchar(30) NOt NULL,

FATHERS_NAME varchar(30),

MOTHERS_NAME varchar(30),

LAST_NAME varchar(30) NOt NULL,

BLOOD_GROUP varchar(4),

ADDRESS varchar(200) NOt NULL,

PHONE varchar(10) Not Null,

MOBILE_ALTERNATE varchar(10),

GMAIL varchar(100) Not Null,

IN_BOARDS varchar(3),

STREAM varchar(12),

SUBJECTS_TAKEN varchar(150));

CREATE TABLE grade1

(ADMISSION_NO VARCHAR(15),

NAME VARCHAR(100),

FOREIGN KEY (ADMISSION_NO)

REFERENCES STUDENT_STATS(ADMISSION_NO));

CREATE TABLE grade2

(ADMISSION_NO VARCHAR(15),

NAME VARCHAR(100),

FOREIGN KEY (ADMISSION_NO)

REFERENCES STUDENT_STATS(ADMISSION_NO));

CREATE TABLE grade3

(ADMISSION_NO VARCHAR(15),

NAME VARCHAR(100),

FOREIGN KEY (ADMISSION_NO)

REFERENCES STUDENT_STATS(ADMISSION_NO));

CREATE TABLE grade4

(ADMISSION_NO VARCHAR(15),

NAME VARCHAR(100),

FOREIGN KEY (ADMISSION_NO)

REFERENCES STUDENT_STATS(ADMISSION_NO));

CREATE TABLE grade5

(ADMISSION_NO VARCHAR(15),

NAME VARCHAR(100),

FOREIGN KEY (ADMISSION_NO)

REFERENCES STUDENT_STATS(ADMISSION_NO));

CREATE TABLE grade6

(ADMISSION_NO VARCHAR(15),

NAME VARCHAR(100),

FOREIGN KEY (ADMISSION_NO)

REFERENCES STUDENT_STATS(ADMISSION_NO));

CREATE TABLE grade7

(ADMISSION_NO VARCHAR(15),

NAME VARCHAR(100),

FOREIGN KEY (ADMISSION_NO)

REFERENCES STUDENT_STATS(ADMISSION_NO));

CREATE TABLE grade8

(ADMISSION_NO VARCHAR(15),

NAME VARCHAR(100),

FOREIGN KEY (ADMISSION_NO)

REFERENCES STUDENT_STATS(ADMISSION_NO));

CREATE TABLE grade9

(ADMISSION_NO VARCHAR(15),

NAME VARCHAR(100),

FOREIGN KEY (ADMISSION_NO)

REFERENCES STUDENT_STATS(ADMISSION_NO));

CREATE TABLE grade10

(ADMISSION_NO VARCHAR(15),

NAME VARCHAR(100),

FOREIGN KEY (ADMISSION_NO)

REFERENCES STUDENT_STATS(ADMISSION_NO));

CREATE TABLE grade11

(ADMISSION_NO VARCHAR(15),

NAME VARCHAR(100),

FOREIGN KEY (ADMISSION_NO)

REFERENCES STUDENT_STATS(ADMISSION_NO));

CREATE TABLE grade12

(ADMISSION_NO VARCHAR(15),

NAME VARCHAR(100),

FOREIGN KEY (ADMISSION_NO)

REFERENCES STUDENT_STATS(ADMISSION_NO));

============================================================================================================================================

create database hospital_management;

use hospital_management;

create table user_auth

(PATIENT_ID Varchar(30) Primary Key,

PWD varchar(15) Not Null);

create table appointments

(PATIENT_ID Varchar(30) NOT NULL,

TIMESTAMP DATETIME,

FOREIGN KEY(PATIENT_ID)

REFERENCES USER_AUTH(PATIENT_ID));

create table patients

(PATIENT_ID VARCHAR(30) NOT NULL,

NAME VARCHAR(50) NOT NULL,

MOBILE VARCHAR(13) NOT NULL,

FOREIGN KEY (PATIENT_ID)

REFERENCES USER_AUTH(PATIENT_ID));

create table due_bills

(PATIENT_ID Varchar(30) NOT NULL,

AMOUNT INT,

FOREIGN KEY(PATIENT_ID)

REFERENCES USER_AUTH(PATIENT_ID));

============================================================================================================================================

print('\n\nPRESS \'d\' TO CAPTURE IMAGE.\n\
MAKE SURE THAT CAPSLOCK IS TURNED OFF.\n\
NOTE: OPEN THE NEW WINDOW APPEARED IN TASKBAR.')

line 85 above "while True:"

>> use .lower() in add_new_stud and staff for the input of ensuring mobile numbers are correct and for ensuring data to be added in add_new_std.

print("""
        _____________________________________________________________________
        |-------------------------------------------------------------------|
        |                       AVAILABLE COMMANDS                          |
        |                                                                   |
        | 1.) If you want all details for this student, Enter "all".        |
        | 2.) For getting Contact Details, Enter "contact".                 |
        | 3.) If you want to delete all data permanently, Enter "delete".   |
        | 4.) If you want to quit, Enter "quit".                            |
        |                                                                   |
        |___________________________________________________________________|
            """)

remove this from if_match and paste in existing_stud

>> use <or '1','2','3' and '4' > with task name to prevent misunderstanding of running task and do the same for existing_staff.

match found then print this above imshow in unknown face check.

print("\n\nCLOSE THE RESULT WINDOW TO PROCEED AHEAD.")

+======================================================================================================================================================================================================================================================================================================

CREATE DATABASE ATM;

USE ATM;

CREATE TABLE ACCOUNTS

(CardNo VARCHAR(20) PRIMARY KEY,

PIN VARCHAR(4) NOT NULL,

NAME VARCHAR(30) NOT NULL,

BALANCE INT,

STATEMENT VARCHAR(100));

Comments