# ---
# jupyter:
#   jupytext:
#     text_representation:
#       extension: .py
#       format_name: light
#       format_version: '1.5'
#       jupytext_version: 1.14.4
#   kernelspec:
#     display_name: Python 3 (ipykernel)
#     language: python
#     name: python3
# ---

# # import sql library

import pandas as pd
import sqlite3

# %load_ext sql


# %sql sqlite://

# # SQl table 1

# + language="sql"
# create table Employee(Emp_id varchar(10) PRIMARY KEY, First name varchar(50) not null, Last name varchar(50), Address varchar(50) not null, title char(25) not null, Salary number(10) not null, Hire_date DATE)

# + language="sql"
# SELECT * FROM employee;
#

# + language="sql"
# insert into Employee values ('emp03', 'rani', 'singh', '43 law gate', 'tester',77000,'2020-06-18');
# insert into Employee values ('emp04', 'kittu', 'singh', '43 law gate', 'desiner',57000,'2020-03-04');
# insert into Employee values ('emp05', 'Aman', 'singh', '43 law gate', 'developer',67000,'2020-08-07');
# insert into Employee values ('emp06', 'riya', 'singh', '43 law gate', 'tester',74000,'2020-03-04');
# insert into Employee values ('emp07', 'Ayush', 'singh', '43 law gate', 'tester',47000,'2020-09-13');
# insert into Employee values ('emp08', 'Nupur', 'singh', '43 law gate', 'developer',37000,'2020-0-19');

# + language="sql"
# SELECT * FROM employee;
#
# -

# # sql table 2

# + language="sql"
# create table Employee2(Emp_id varchar(10) PRIMARY KEY, city_cover varchar(50) not null, Contact_number number(10) not null, Age int,
#     CHECK (Age>=18) );

# + language="sql"
#
# insert into Employee2 values ('emp03', 'kanpur', 8051557283,24);
# insert into Employee2 values ('emp04', 'jalandher', 8051557283,22);
# insert into Employee2 values ('emp05', 'chandighar', 8051557283,29);
# insert into Employee2 values ('emp06', 'patna', 8051557283,26);
# insert into Employee2 values ('emp07', 'Delhi', 8051557283,30);
# insert into Employee2 values ('emp08', 'kanpur', 8051557283,31);
#

# + language="sql"
# SELECT * FROM employee2;
# -

# # inner join

# + language="sql"
# select Employee.first,Employee.last,Employee2.city_cover from Employee INNER JOIN Employee2 ON Employee.Emp_id = Employee2.Emp_id;
# -

# # view

# + language="sql"
# CREATE VIEW DetailsView AS
# SELECT first, last, title 
# FROM Employee;
# WHERE Emp_id = emp05;

# + language="sql"
# SELECT * FROM DetailsView;
# -

# # like clause  

# + language="sql"
# select * from Employee where title like '__v%';
# -

# # Order by clause 

# + language="sql"
# select * from Employee order by salary;

# + language="sql"
# select * from Employee order by salary desc;

StarcoderdataJupyterScriptsDedupFiltered