SQL Edge Basic → Pro

CREATE DATABASE school;

CREATE TABLE students (id INT, name VARCHAR(50));

INSERT INTO students VALUES (1, "Amit");

SELECT * FROM students;

SELECT * FROM students WHERE id=1;

UPDATE students SET name="Kapil" WHERE id=1;

DELETE FROM students WHERE id=1;

SELECT * FROM students ORDER BY name ASC;

SELECT * FROM students LIMIT 5;

SELECT DISTINCT name FROM students;

SELECT * FROM students WHERE id>1 AND name="Kapil";

SELECT * FROM students WHERE id IN (1,2,3);

SELECT * FROM students WHERE id BETWEEN 1 AND 5;

SELECT * FROM students WHERE name LIKE "A%";

SELECT * FROM students WHERE name IS NOT NULL;

SELECT name AS student_name FROM students;

SELECT s.name FROM students AS s;

SELECT * FROM students s INNER JOIN marks m ON s.id=m.stu_id;

SELECT * FROM students s LEFT JOIN marks m ON s.id=m.stu_id;

SELECT * FROM students s RIGHT JOIN marks m ON s.id=m.stu_id;

SELECT * FROM students s FULL OUTER JOIN marks m ON s.id=m.stu_id;

SELECT * FROM students CROSS JOIN marks;

SELECT a.name, b.name FROM employees a, employees b WHERE a.manager_id=b.id;

SELECT stu_id, COUNT(*) FROM marks GROUP BY stu_id;

SELECT stu_id, AVG(marks) FROM marks GROUP BY stu_id HAVING AVG(marks)>50;

SELECT COUNT(*) FROM students;

SELECT SUM(marks) FROM marks;

SELECT AVG(marks) FROM marks;

SELECT MIN(marks), MAX(marks) FROM marks;

SELECT CONCAT(name," Kumar") FROM students;

SELECT SUBSTRING(name,1,3) FROM students;

SELECT TRIM("   Kapil   ");

SELECT UPPER(name), LOWER(name) FROM students;

SELECT REPLACE(name,"Kapil","Amit") FROM students;

SELECT NOW(), CURDATE();

SELECT DATEDIFF("2025-10-05","2025-10-01");

SELECT DATE_ADD(CURDATE(), INTERVAL 5 DAY);

SELECT name, CASE WHEN marks>50 THEN "Pass" ELSE "Fail" END FROM marks;

SELECT name FROM students UNION SELECT name FROM teachers;

SELECT name FROM students UNION ALL SELECT name FROM teachers;

SELECT * FROM students s WHERE EXISTS (SELECT * FROM marks m WHERE m.stu_id=s.id);

SELECT * FROM students s WHERE NOT EXISTS (SELECT * FROM marks m WHERE m.stu_id=s.id);

SELECT * FROM students WHERE id IN (SELECT stu_id FROM marks);

SELECT * FROM students WHERE id NOT IN (SELECT stu_id FROM marks);

SELECT name FROM students WHERE id=(SELECT MAX(stu_id) FROM marks);

CREATE INDEX idx_name ON students(name);

ALTER TABLE students ADD PRIMARY KEY(id);

ALTER TABLE marks ADD FOREIGN KEY (stu_id) REFERENCES students(id);

ALTER TABLE students ADD UNIQUE(email);

ALTER TABLE students MODIFY name VARCHAR(50) NOT NULL;

ALTER TABLE students ADD COLUMN status VARCHAR(10) DEFAULT "Active";

ALTER TABLE students ADD CONSTRAINT chk_age CHECK (age>=18);

TRUNCATE TABLE students;

DROP TABLE students;

ALTER TABLE students RENAME TO student_info;

ALTER TABLE students RENAME COLUMN name TO student_name;

ALTER TABLE students ADD COLUMN dob DATE;

ALTER TABLE students DROP COLUMN dob;

ALTER TABLE students MODIFY COLUMN name VARCHAR(100);

START TRANSACTION;
UPDATE students SET name="Kapil" WHERE id=1;
ROLLBACK;

START TRANSACTION;
UPDATE students SET name="Kapil" WHERE id=1;
COMMIT;

SAVEPOINT sp1;
UPDATE students SET name="Amit" WHERE id=1;
ROLLBACK TO sp1;

EXPLAIN SELECT * FROM students;

SELECT COUNT(*) AS total_students FROM students;

SELECT COUNT(DISTINCT name) FROM students;

# Multiclass Logistic Regression Example
import pandas as pd
from sklearn.model_selection import train_test_split
from sklearn.linear_model import LogisticRegression
from sklearn.preprocessing import StandardScaler
from sklearn.metrics import classification_report, confusion_matrix

# Load the dataset
df = pd.read_csv("multiclass_product_data.csv")
print(df.head())

# Encode categorical variables
df = pd.get_dummies(df, columns=["Product_Category", "Store_Size"], drop_first=True)

# Define features and target
X = df.drop("Target_Product_Type", axis=1)
y = df["Target_Product_Type"]

# Train-test split
X_train, X_test, y_train, y_test = train_test_split(X, y, test_size=0.2, random_state=42)

# Feature scaling
scaler = StandardScaler()
X_train_scaled = scaler.fit_transform(X_train)
X_test_scaled = scaler.transform(X_test)

# Train One-vs-Rest Logistic Regression
ovr_model = LogisticRegression(multi_class="ovr", solver="liblinear")
ovr_model.fit(X_train_scaled, y_train)
y_pred_ovr = ovr_model.predict(X_test_scaled)

# Train One-vs-One Logistic Regression
ovo_model = LogisticRegression(multi_class="ovo", solver="liblinear")
ovo_model.fit(X_train_scaled, y_train)
y_pred_ovo = ovo_model.predict(X_test_scaled)

# Evaluate both models
print("One-vs-Rest Classification Report:")
print(classification_report(y_test, y_pred_ovr))
print("One-vs-One Classification Report:")
print(classification_report(y_test, y_pred_ovo))

# Confusion Matrix for OvR
cm_ovr = confusion_matrix(y_test, y_pred_ovr)
print("Confusion Matrix (OvR):")
print(cm_ovr)

SELECT name, (SELECT AVG(marks) FROM marks WHERE stu_id=students.id) AS avg_marks FROM students;

SELECT name FROM students s WHERE EXISTS (SELECT * FROM marks m WHERE m.stu_id=s.id AND m.marks>50);

UPDATE students SET status="Inactive" WHERE id>5;

UPDATE students SET status=CASE WHEN id=1 THEN "Active" ELSE "Inactive" END;

DELETE FROM students ORDER BY id DESC LIMIT 1;

UPDATE students SET status="Test" ORDER BY id ASC LIMIT 1;

-- This is a comment
SELECT * FROM students;

/* Comment 1
Comment 2 */
SELECT * FROM students;

CREATE TEMPORARY TABLE temp_students (id INT, name VARCHAR(50));

SELECT IFNULL(name,"No Name") FROM students;

SELECT COUNT(CASE WHEN marks>50 THEN 1 END) AS passed_students FROM marks;

CREATE PROCEDURE get_students() BEGIN SELECT * FROM students; END;

CALL get_students();

CREATE FUNCTION total_marks(stu INT) RETURNS INT BEGIN DECLARE t INT; SELECT SUM(marks) INTO t FROM marks WHERE stu_id=stu; RETURN t; END;

CREATE TRIGGER before_insert_student BEFORE INSERT ON students FOR EACH ROW SET NEW.name=UPPER(NEW.name);

CREATE VIEW student_info AS SELECT s.name, m.marks FROM students s JOIN marks m ON s.id=m.stu_id;

GRANT SELECT, INSERT ON school.* TO "user"@"localhost";

REVOKE INSERT ON school.* FROM "user"@"localhost";

mysqldump -u root -p school > school.sql

mysql -u root -p school < school.sql

LOAD DATA INFILE "students.csv" INTO TABLE students FIELDS TERMINATED BY "," LINES TERMINATED BY "\n";

SELECT * FROM students INTO OUTFILE "students.csv" FIELDS TERMINATED BY "," LINES TERMINATED BY "\n";

EXPLAIN SELECT * FROM students;

CREATE INDEX idx_marks ON marks(marks);

CREATE INDEX idx_student_marks ON marks(stu_id, marks);

CREATE FULLTEXT INDEX idx_name ON students(name);

CREATE VIEW student_marks AS SELECT s.name, m.marks FROM students s JOIN marks m ON s.id=m.stu_id;

WITH cte AS (SELECT * FROM students) SELECT * FROM cte;

WITH RECURSIVE emp_hierarchy AS (SELECT * FROM employees WHERE manager_id IS NULL UNION ALL SELECT e.* FROM employees e JOIN emp_hierarchy h ON e.manager_id=h.id) SELECT * FROM emp_hierarchy;

SELECT name, marks, RANK() OVER(PARTITION BY course_id ORDER BY marks DESC) AS rank FROM marks;

SELECT name, ROW_NUMBER() OVER(ORDER BY marks DESC) AS row_num FROM marks;

SELECT name, RANK() OVER(ORDER BY marks DESC) AS rank FROM marks;

SELECT name, DENSE_RANK() OVER(ORDER BY marks DESC) AS drank FROM marks;

SELECT name, NTILE(4) OVER(ORDER BY marks DESC) AS quartile FROM marks;

SELECT name, marks, LAG(marks) OVER(ORDER BY marks) AS prev_marks FROM marks;

SELECT name, FIRST_VALUE(marks) OVER(ORDER BY marks) AS first_mark FROM marks;

PREPARE stmt FROM "SELECT * FROM students WHERE id=?";

SET @total_marks = (SELECT SUM(marks) FROM marks);

CREATE PROCEDURE get_marks(IN stu INT, OUT total INT) BEGIN SELECT SUM(marks) INTO total FROM marks WHERE stu_id=stu; END;

DECLARE EXIT HANDLER FOR SQLEXCEPTION ROLLBACK;

CREATE TRIGGER log_update AFTER UPDATE ON students FOR EACH ROW INSERT INTO audit_table VALUES (OLD.name, NEW.name, NOW());

SELECT * FROM t1 JOIN t2 ON ... JOIN t3 ON ...;

SELECT SUM(CASE WHEN marks>50 THEN 1 ELSE 0 END) AS passed FROM marks;

SELECT name, RANK() OVER(ORDER BY marks DESC) AS rnk FROM marks;

EXPLAIN SELECT * FROM marks WHERE marks>50;

EXPLAIN SELECT ...;

INSERT INTO students (id,name) VALUES (1,"Amit"),(2,"Kapil");

UPDATE students SET status="Active" WHERE id IN (1,2,3);

DELETE s FROM students s JOIN marks m ON s.id=m.stu_id WHERE m.marks<40;

SELECT DATE_FORMAT(CURDATE(),"%Y-%m-%d");

SELECT * FROM students WHERE name REGEXP "^[A-Z]";

CREATE TABLE t (id INT, data JSON);

SELECT JSON_EXTRACT(data,"$.name") FROM t;

SELECT SUM(marks) OVER(PARTITION BY course_id) AS total FROM marks;

SELECT * FROM students FOR UPDATE;

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;