Scribd
Upload
10 views4 pages

Essential SQL Commands and Queries

The document outlines SQL commands and queries for basic database operations, including creating and managing databases and tables, as well as performing insert, update, and delete operations. It provides examples of various queries to retrieve specific data, such as students in a particular semester, borrowed books, and authors. Additionally, it includes advanced queries for grouping and filtering results based on conditions like the number of books borrowed and publishers.

Uploaded by

harisha21204
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
10 views4 pages

Essential SQL Commands and Queries

The document outlines SQL commands and queries for basic database operations, including creating and managing databases and tables, as well as performing insert, update, and delete operations. It provides examples of various queries to retrieve specific data, such as students in a particular semester, borrowed books, and authors. Additionally, it includes advanced queries for grouping and filtering results based on conditions like the number of books borrowed and publishers.

Uploaded by

harisha21204
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

SQL Commands and Queries

1. Basic DB Operations:

-----------------------

Viewing all Databases:

MySQL:

SHOW DATABASES;

Oracle:

SELECT username FROM all_users;

Create Database:

CREATE DATABASE collegeDB;

USE collegeDB;

View all Tables:

SHOW TABLES;

Create Tables:

Without Constraints:

CREATE TABLE student (

usn VARCHAR(20),

name VARCHAR(50),

date_of_birth DATE,

branch VARCHAR(20),

mark1 INT,

mark2 INT,

mark3 INT,

total INT,

gpa FLOAT,

sem INT
);

With Constraints:

CREATE TABLE author (

authorid INT PRIMARY KEY,

authorname VARCHAR(50),

country VARCHAR(30),

age INT

);

CREATE TABLE book (

bookid INT PRIMARY KEY,

bookname VARCHAR(100),

authorid INT,

publisher VARCHAR(50),

branch VARCHAR(20),

FOREIGN KEY (authorid) REFERENCES author(authorid)

);

CREATE TABLE borrowed (

usn VARCHAR(20),

bookid INT,

borrowed_date DATE,

FOREIGN KEY (usn) REFERENCES student(usn),

FOREIGN KEY (bookid) REFERENCES book(bookid)

);

Insert Records:

INSERT INTO student VALUES ('1BCA01','Ravi','2004-05-10','BCA',85,75,90,250,8.5,2);

Update Record:

UPDATE student SET mark1 = 95 WHERE usn = '1BCA01';


Delete Record:

DELETE FROM student WHERE usn = '1BCA02';

Commit:

COMMIT;

Rollback:

ROLLBACK;

2. Queries:

-----------

a. Students studying in 2nd sem BCA:

SELECT * FROM student WHERE branch='BCA' AND sem=2;

b. Students who have not borrowed any books:

SELECT s.* FROM student s LEFT JOIN borrowed b ON [Link]=[Link] WHERE [Link] IS NULL;

3. Queries:

-----------

a. USN, name, branch, book name, author name, borrowed date:

SELECT [Link], [Link], [Link], [Link], [Link], b.borrowed_date

FROM student s

JOIN borrowed b ON [Link]=[Link]

JOIN book bk ON [Link]=[Link]

JOIN author a ON [Link]=[Link]

WHERE [Link]=2 AND [Link]='BCA';

b. Number of books written by each author:

SELECT [Link], COUNT([Link])

FROM author a LEFT JOIN book b ON [Link]=[Link]

GROUP BY [Link];
4. Queries:

-----------

a. Students who borrowed more than two books:

SELECT s.* FROM student s

JOIN borrowed b ON [Link]=[Link]

GROUP BY [Link] HAVING COUNT([Link])>2;

b. Students who borrowed books of more than one author:

SELECT s.* FROM student s

JOIN borrowed br ON [Link]=[Link]

JOIN book bk ON [Link]=[Link]

GROUP BY [Link] HAVING COUNT(DISTINCT [Link])>1;

5. Queries:

-----------

a. Book names in descending order:

SELECT bookname FROM book ORDER BY bookname DESC;

b. Student details who borrowed books published by the same publisher:

SELECT s.*, [Link]

FROM student s

JOIN borrowed br ON [Link]=[Link]

JOIN book bk ON [Link]=[Link]

GROUP BY [Link], [Link] HAVING COUNT(*)>=1;

You might also like