MongoDB Commands for Employee Database

1. Use/Create the Employee Database

use employeeDB

2. Create the Employees Collection (optional, will auto-

create on insert)
db.createCollection("employees")

3. Insert Sample Employee Documents (including ‘Rahul’)

db.employees.insertMany([
{
eid: 1,
ename: "Rahul",
dept: "IT",
desig: "developer",
salary: 65000,
yoj: 2021,
address: { dno: "12A", street: "MG Road", locality: "Sector 5", city: "Mumbai" }
},
{
eid: 2,
ename: "Anita",
dept: "HR",
desig: "manager",
salary: 80000,
yoj: 2019,
address: { dno: "7B", street: "Park Street", locality: "Downtown", city: "Delhi" }
},
{
eid: 3,
ename: "Vikram",
dept: "Finance",
desig: "analyst",
salary: 70000,
yoj: 2020,
address: { dno: "33C", street: "Lake View", locality: "Sector 2", city: "Bangalore" }
},
{
eid: 4,
ename: "Priya",
dept: "IT",
desig: "developer",

1
 salary: 60000,
yoj: 2022,
address: { dno: "44D", street: "Central Ave", locality: "Midtown", city: "Pune" }
}
])

4. Queries and Updates

(i) Display all employees with salary between 50,000 and 75,000
db.employees.find({ salary: { $gte: 50000, $lte: 75000 } })

(ii) Display all employees with designation ‘developer’

db.employees.find({ desig: "developer" })

(iii) Add an array field ‘project’ to ‘Rahul’

db.employees.updateOne(
{ ename: "Rahul" },
{ $set: { project: [] } }
)

(iv) Add a new embedded object ‘contacts’ and ‘phone’ (as array) to
‘Rahul’
db.employees.updateOne(
{ ename: "Rahul" },
{ $set: { contacts: { phone: [] } } }
)

How to generate PDF: 1. Copy this Markdown into your Markdown editor
or word processor. 2. Use the “Export as PDF” or “Print to PDF” function.

2
MongoDB Commands for Library Information
System (with Expected Output)

1. Create/Use the Library Database

use libraryDB
Output:
switched to db libraryDB

2. Create the ‘books’ Collection (optional)

db.createCollection("books")
Output:
{ "ok" : 1 }

3. Insert Sample Book Documents (Create)

db.books.insertMany([
{
isbn: "978-0140449136",
title: "The Odyssey",
author: "Homer",
year: -700,
genre: "Epic",
copies: 5,
publisher: "Penguin Classics",
location: { section: "Classics", shelf: 3 }
},
{
isbn: "978-0061120084",
title: "To Kill a Mockingbird",
author: "Harper Lee",
year: 1960,
genre: "Fiction",
copies: 2,
publisher: "Harper Perennial",
location: { section: "Fiction", shelf: 1 }
}
])

1
Output:
{
"acknowledged" : true,
"insertedIds" : [
ObjectId("..."),
ObjectId("...")
]
}

4. Read (Query) Documents

a) Find All Books
db.books.find()
Output:
[
{
"_id": ObjectId("..."),
"isbn": "978-0140449136",
"title": "The Odyssey",
"author": "Homer",
"year": -700,
"genre": "Epic",
"copies": 5,
"publisher": "Penguin Classics",
"location": { "section": "Classics", "shelf": 3 }
},
{
"_id": ObjectId("..."),
"isbn": "978-0061120084",
"title": "To Kill a Mockingbird",
"author": "Harper Lee",
"year": 1960,
"genre": "Fiction",
"copies": 2,
"publisher": "Harper Perennial",
"location": { "section": "Fiction", "shelf": 1 }
}
]

b) Find Books by Author

db.books.find({ author: "Harper Lee" })

2
Output:
[
{
"_id": ObjectId("..."),
"isbn": "978-0061120084",
"title": "To Kill a Mockingbird",
"author": "Harper Lee",
"year": 1960,
"genre": "Fiction",
"copies": 2,
"publisher": "Harper Perennial",
"location": { "section": "Fiction", "shelf": 1 }
}
]

c) Find a Book by ISBN

db.books.findOne({ isbn: "978-0140449136" })
Output:
{
"_id": ObjectId("..."),
"isbn": "978-0140449136",
"title": "The Odyssey",
"author": "Homer",
"year": -700,
"genre": "Epic",
"copies": 5,
"publisher": "Penguin Classics",
"location": { "section": "Classics", "shelf": 3 }
}

5. Update Documents
a) Update Number of Copies for a Book
db.books.updateOne(
{ isbn: "978-0140449136" },
{ $set: { copies: 7 } }
)
Output:
{
"acknowledged" : true,

3
 "matchedCount" : 1,
"modifiedCount" : 1
}

b) Add a New Field (e.g., ‘available’) to All Books

db.books.updateMany(
{},
{ $set: { available: true } }
)
Output:
{
"acknowledged" : true,
"matchedCount" : 2,
"modifiedCount" : 2
}

6. Delete Documents
a) Delete a Book by ISBN
db.books.deleteOne({ isbn: "978-0061120084" })
Output:
{
"acknowledged" : true,
"deletedCount" : 1
}

b) Delete All Books of a Genre

db.books.deleteMany({ genre: "Epic" })
Output:
{
"acknowledged" : true,
"deletedCount" : 1
}

7. Indexing
a) Create Index on ISBN (unique)
db.books.createIndex({ isbn: 1 }, { unique: true })

4
Output:
"ISBN_1"

b) Create Index on Author

db.books.createIndex({ author: 1 })
Output:
"author_1"

8. Sharding (requires a sharded cluster, run in mongos

shell)
a) Enable Sharding on the Database
sh.enableSharding("libraryDB")
Output:
{ "ok" : 1 }

b) Shard the ‘books’ Collection on ‘isbn’

sh.shardCollection("libraryDB.books", { isbn: 1 })
Output:
{ "collectionsharded" : "libraryDB.books", "ok" : 1 }

Note:
- Sharding commands and output require a running sharded cluster and the
mongos shell. - Output may slightly vary depending on MongoDB version.

5
-- 1. SQL: Create the Table
CREATE TABLE images (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(255) NOT NULL,
data LONGBLOB NOT NULL
);

# 2. Python: Store and Retrieve Image Data

import mysql.connector

def connect():
return mysql.connector.connect(
host="localhost",
user="your_username", # Change to your MySQL username
password="your_password", # Change to your MySQL password
database="your_database" # Change to your database name
)

def store_image(file_path, image_name):

conn = connect()
cursor = conn.cursor()
with open(file_path, 'rb') as f:
binary_data = f.read()
sql = "INSERT INTO images (name, data) VALUES (%s, %s)"
cursor.execute(sql, (image_name, binary_data))
conn.commit()
cursor.close()
conn.close()
print(f"Stored image '{image_name}'.")

def retrieve_image(image_id, output_path):

conn = connect()
cursor = conn.cursor()
sql = "SELECT data, name FROM images WHERE id = %s"
cursor.execute(sql, (image_id,))
row = cursor.fetchone()
cursor.close()
conn.close()
if row:
binary_data, image_name = row
with open(output_path, 'wb') as f:
f.write(binary_data)
print(f"Image '{image_name}' written to '{output_path}'.")
else:
print("Image not found.")

# Example Usage:
# store_image('input.jpg', 'sample_image')
# retrieve_image(1, 'retrieved.jpg')

# --- Example Output ---

# If you run: store_image('input.jpg', 'sample_image')
# Output:
# Stored image 'sample_image'.

# If you run: retrieve_image(1, 'retrieved.jpg')

# Output (if the image exists):
# Image 'sample_image' written to 'retrieved.jpg'.
# Output (if the image does not exist):
# Image not found.

# --- End of File ---

-- 1. Database creation
CREATE DATABASE temporal_example_db;
USE temporal_example_db;

-- 2. Table creation
CREATE TABLE temporal_data (
id INT AUTO_INCREMENT PRIMARY KEY,
event_time DATETIME NOT NULL,
data VARCHAR(255) NOT NULL
) ENGINE=InnoDB;

-- 3. Data insertion
INSERT INTO temporal_data (event_time, data) VALUES
('2025-05-27 10:00:00', 'First event'),
('2025-05-27 12:30:00', 'Second event'),
('2025-05-28 09:15:00', 'Third event'),
('2025-05-29 18:45:00', 'Fourth event'),
('2025-05-30 08:00:00', 'Fifth event');

-- 4. Retrieve all records

SELECT * FROM temporal_data;
-- Output:
-- +----+---------------------+-------------+
-- | id | event_time | data |
-- +----+---------------------+-------------+
-- | 1 | 2025-05-27 10:00:00 | First event |
-- | 2 | 2025-05-27 12:30:00 | Second event|
-- | 3 | 2025-05-28 09:15:00 | Third event |
-- | 4 | 2025-05-29 18:45:00 | Fourth event|
-- | 5 | 2025-05-30 08:00:00 | Fifth event |
-- +----+---------------------+-------------+

-- 5. Retrieve events after a specific time

SELECT * FROM temporal_data
WHERE event_time > '2025-05-27 11:00:00';
-- Output:
-- +----+---------------------+-------------+
-- | id | event_time | data |
-- +----+---------------------+-------------+
-- | 2 | 2025-05-27 12:30:00 | Second event|
-- | 3 | 2025-05-28 09:15:00 | Third event |
-- | 4 | 2025-05-29 18:45:00 | Fourth event|
-- | 5 | 2025-05-30 08:00:00 | Fifth event |
-- +----+---------------------+-------------+

-- 6. Retrieve events within a date range

SELECT * FROM temporal_data
WHERE event_time BETWEEN '2025-05-28 00:00:00' AND '2025-05-29
23:59:59';
-- Output:
-- +----+---------------------+-------------+
-- | id | event_time | data |
-- +----+---------------------+-------------+
-- | 3 | 2025-05-28 09:15:00 | Third event |
-- | 4 | 2025-05-29 18:45:00 | Fourth event|
-- +----+---------------------+-------------+

-- 7. Retrieve the most recent event

SELECT * FROM temporal_data
ORDER BY event_time DESC
LIMIT 1;
-- Output:
-- +----+---------------------+-------------+
-- | id | event_time | data |
-- +----+---------------------+-------------+
-- | 5 | 2025-05-30 08:00:00 | Fifth event |
-- +----+---------------------+-------------+

-- 8. Count events per day

SELECT DATE(event_time) AS event_date, COUNT(*) AS event_count
FROM temporal_data
GROUP BY event_date
ORDER BY event_date;
-- Output:
-- +------------+-------------+
-- | event_date | event_count |
-- +------------+-------------+
-- | 2025-05-27 | 2 |
-- | 2025-05-28 | 1 |
-- | 2025-05-29 | 1 |
-- | 2025-05-30 | 1 |
-- +------------+-------------+

-- 9. Retrieve events for a specific day of the week (e.g., Friday,

where 6 = Friday)
SELECT * FROM temporal_data
WHERE DAYOFWEEK(event_time) = 6;
-- Output:
-- +----+---------------------+-------------+
-- | id | event_time | data |
-- +----+---------------------+-------------+
-- | 4 | 2025-05-29 18:45:00 | Fourth event|
-- +----+---------------------+-------------+

-- 10. Calculate time difference between consecutive events

SELECT id, event_time, data,
TIMESTAMPDIFF(MINUTE, LAG(event_time) OVER (ORDER BY
event_time), event_time) AS minutes_since_last_event
FROM temporal_data
ORDER BY event_time;
-- Output:
-- +----+---------------------+-------------+--------------------------
+
-- | id | event_time | data | minutes_since_last_event
|
-- +----+---------------------+-------------+--------------------------
+
-- | 1 | 2025-05-27 10:00:00 | First event | NULL
|
-- | 2 | 2025-05-27 12:30:00 | Second event| 150
|
-- | 3 | 2025-05-28 09:15:00 | Third event | 975
|
-- | 4 | 2025-05-29 18:45:00 | Fourth event| 2010
|
-- | 5 | 2025-05-30 08:00:00 | Fifth event | 795
|
-- +----+---------------------+-------------+--------------------------
+
-- 1. Database creation
CREATE DATABASE temporal_example_db;
USE temporal_example_db;

-- 2. Table creation (InnoDB, no special temporal clauses)

CREATE TABLE temporal_data (
id INT AUTO_INCREMENT PRIMARY KEY,
event_time DATETIME NOT NULL,
data VARCHAR(255) NOT NULL
) ENGINE=InnoDB;

-- 3. Example data insertion (temporal data)

INSERT INTO temporal_data (event_time, data) VALUES
('2025-05-27 10:00:00', 'First event'),
('2025-05-27 12:30:00', 'Second event'),
('2025-05-28 09:15:00', 'Third event');

-- 4. Example query: Retrieve all records

SELECT * FROM temporal_data;

-- Example output:
-- +----+---------------------+-------------+
-- | id | event_time | data |
-- +----+---------------------+-------------+
-- | 1 | 2025-05-27 10:00:00 | First event |
-- | 2 | 2025-05-27 12:30:00 | Second event|
-- | 3 | 2025-05-28 09:15:00 | Third event |
-- +----+---------------------+-------------+

-- 5. Example query: Retrieve events after a specific time

SELECT * FROM temporal_data
WHERE event_time > '2025-05-27 11:00:00';

-- Example output:
-- +----+---------------------+-------------+
-- | id | event_time | data |
-- +----+---------------------+-------------+
-- | 2 | 2025-05-27 12:30:00 | Second event|
-- | 3 | 2025-05-28 09:15:00 | Third event |
-- +----+---------------------+-------------+
-- 1. Create the database
CREATE DATABASE IF NOT EXISTS spatial_test_db;

-- 1.1 OUTPUT for CREATE DATABASE:

-- Query OK, 1 row affected (0.01 sec)

-- 1.2 Show databases to verify creation

SHOW DATABASES;

-- Example OUTPUT for SHOW DATABASES:

-- +--------------------+
-- | Database |
-- +--------------------+
-- | information_schema |
-- | mysql |
-- | performance_schema |
-- | spatial_test_db |
-- | sys |
-- +--------------------+

-- 2. Use the database

USE spatial_test_db;

-- 2.1 OUTPUT for USE:

-- Database changed

-- 2.2 Show current database

SELECT DATABASE();

-- Example OUTPUT for SELECT DATABASE():

-- +------------------+
-- | DATABASE() |
-- +------------------+
-- | spatial_test_db |
-- +------------------+

-- 3. Create the 'geotest' table with spatial columns

CREATE TABLE geotest (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100),
geom GEOMETRY,
location POINT,
route LINESTRING,
area POLYGON
) ENGINE=InnoDB;

-- 3.1 OUTPUT for CREATE TABLE:

-- Query OK, 0 rows affected (0.02 sec)

-- 3.2 Show tables to verify creation

SHOW TABLES;

-- Example OUTPUT for SHOW TABLES:

-- +-------------------+
-- | Tables_in_spatial_test_db |
-- +-------------------+
-- | geotest |
-- +-------------------+
-- 3.3 Show table structure
DESCRIBE geotest;

-- Example OUTPUT for DESCRIBE geotest:

-- +----------+--------------+------+-----+---------+----------------+
-- | Field | Type | Null | Key | Default | Extra |
-- +----------+--------------+------+-----+---------+----------------+
-- | id | int(11) | NO | PRI | NULL | auto_increment |
-- | name | varchar(100) | YES | | NULL | |
-- | geom | geometry | YES | | NULL | |
-- | location | point | YES | | NULL | |
-- | route | linestring | YES | | NULL | |
-- | area | polygon | YES | | NULL | |
-- +----------+--------------+------+-----+---------+----------------+

-- 4. Insert a sample row with all spatial types

INSERT INTO geotest (name, geom, location, route, area)
VALUES (
'Example Feature',
ST_GeomFromText('POINT(10 20)'),
ST_GeomFromText('POINT(15 25)'),
ST_GeomFromText('LINESTRING(0 0, 10 10, 20 25)'),
ST_GeomFromText('POLYGON((0 0, 10 0, 10 10, 0 10, 0 0))')
);

-- 4.1 OUTPUT for INSERT:

-- Query OK, 1 row affected (0.01 sec)

-- 4.2 Select data to verify insert

SELECT * FROM geotest;

-- Example OUTPUT for SELECT * FROM geotest:

-- +----+------------------+--------+----------+-------------------+---
------------------------+
-- | id | name | geom | location | route |
area |
-- +----+------------------+--------+----------+-------------------+---
------------------------+
-- | 1 | Example Feature | <blob> | <blob> | <blob> |
<blob> |
-- +----+------------------+--------+----------+-------------------+---
------------------------+

-- 5. Select all rows and show spatial columns as WKT (Well-Known Text)
SELECT
id,
name,
ST_AsText(geom) AS geom_wkt,
ST_AsText(location) AS location_wkt,
ST_AsText(route) AS route_wkt,
ST_AsText(area) AS area_wkt
FROM geotest;

-- Example OUTPUT for SELECT with WKT:

-- +----+------------------+-------------+---------------+-------------
----------------+------------------------------------------+
-- | id | name | geom_wkt | location_wkt | route_wkt
| area_wkt |
-- +----+------------------+-------------+---------------+-------------
----------------+------------------------------------------+
-- | 1 | Example Feature | POINT(10 20)| POINT(15 25) | LINESTRING(0
0,10 10,20 25) | POLYGON((0 0,10 0,10 10,0 10,0 0)) |
-- +----+------------------+-------------+---------------+-------------
----------------+------------------------------------------+
-- 0. Create the database
CREATE DATABASE bank_demo;

-- Output:
-- Query OK, 1 row affected

-- 1. Select the database

USE bank_demo;

-- Output:
-- Database changed

-- 2. Create the customer table

CREATE TABLE customer (
customer_id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(100) NOT NULL,
credit_limit DECIMAL(12,2) NOT NULL DEFAULT 0.00
);

-- Output:
-- Query OK, 0 rows affected

-- 3. Insert sample customers

INSERT INTO customer (name, credit_limit) VALUES
('Alice', 60000),
('Bob', 20000),
('Charlie', 5000);

-- Output:
-- Query OK, 3 rows affected

-- 4. Deposit: Add 5000 to Bob's account

UPDATE customer SET credit_limit = credit_limit + 5000 WHERE name =
'Bob';

-- Output:
-- Query OK, 1 row affected

-- 5. Withdrawal: Subtract 3000 from Charlie's account

UPDATE customer SET credit_limit = credit_limit - 3000 WHERE name =
'Charlie';

-- Output:
-- Query OK, 1 row affected

-- 6. Show all customer balances and levels using IF

SELECT
name,
credit_limit AS balance,
IF(credit_limit > 50000, 'PLATINUM',
IF(credit_limit > 10000, 'GOLD', 'SILVER')
) AS customer_level
FROM customer;

-- Output:
-- +---------+----------+----------------+
-- | name | balance | customer_level |
-- +---------+----------+----------------+
-- | Alice | 60000.00 | PLATINUM |
-- | Bob | 25000.00 | GOLD |
-- | Charlie | 2000.00 | SILVER |
-- +---------+----------+----------------+

-- 7. Show all customer balances and levels using CASE

SELECT
name,
credit_limit AS balance,
CASE
WHEN credit_limit > 50000 THEN 'PLATINUM'
WHEN credit_limit > 10000 THEN 'GOLD'
ELSE 'SILVER'
END AS customer_level
FROM customer;

-- Output:
-- +---------+----------+----------------+
-- | name | balance | customer_level |
-- +---------+----------+----------------+
-- | Alice | 60000.00 | PLATINUM |
-- | Bob | 25000.00 | GOLD |
-- | Charlie | 2000.00 | SILVER |
-- +---------+----------+----------------+
-- SOCIAL NETWORKING PLATFORM: MySQL Script with Outputs

-- (Database Creation)
CREATE DATABASE social_networking;
-- Output: Query OK, 1 row affected

USE social_networking;
-- Output: Database changed

-- (i) CREATE TABLES

CREATE TABLE users (

user_id INT AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(50) NOT NULL,
email VARCHAR(100) NOT NULL UNIQUE,
dob DATE,
is_active BOOLEAN DEFAULT 1
);
-- Output: Query OK, 0 rows affected

CREATE TABLE user_profile (

profile_id INT AUTO_INCREMENT PRIMARY KEY,
user_id INT NOT NULL,
bio TEXT,
location VARCHAR(100),
is_active BOOLEAN DEFAULT 1,
FOREIGN KEY (user_id) REFERENCES users(user_id)
);
-- Output: Query OK, 0 rows affected

CREATE TABLE friends (

friendship_id INT AUTO_INCREMENT PRIMARY KEY,
user_id INT NOT NULL,
friend_id INT NOT NULL,
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (user_id) REFERENCES users(user_id),
FOREIGN KEY (friend_id) REFERENCES users(user_id)
);
-- Output: Query OK, 0 rows affected

-- (ii) DESCRIBE TABLES

DESCRIBE users;
-- Output:
-- +----------+--------------+------+-----+---------+----------------+
-- | Field | Type | Null | Key | Default | Extra |
-- +----------+--------------+------+-----+---------+----------------+
-- | user_id | int | NO | PRI | NULL | auto_increment |
-- | username | varchar(50) | NO | | NULL | |
-- | email | varchar(100) | NO | UNI | NULL | |
-- | dob | date | YES | | NULL | |
-- | is_active| tinyint(1) | YES | | 1 | |
-- +----------+--------------+------+-----+---------+----------------+

DESCRIBE user_profile;
-- Output:
-- +-----------+--------------+------+-----+---------+----------------+
-- | Field | Type | Null | Key | Default | Extra |
-- +-----------+--------------+------+-----+---------+----------------+
-- | profile_id| int | NO | PRI | NULL | auto_increment |
-- | user_id | int | NO | MUL | NULL | |
-- | bio | text | YES | | NULL | |
-- | location | varchar(100) | YES | | NULL | |
-- | is_active | tinyint(1) | YES | | 1 | |
-- +-----------+--------------+------+-----+---------+----------------+

DESCRIBE friends;
-- Output:
-- +--------------+--------------+------+-----+-------------------+----
------------+
-- | Field | Type | Null | Key | Default |
Extra |
-- +--------------+--------------+------+-----+-------------------+----
------------+
-- | friendship_id| int | NO | PRI | NULL |
auto_increment |
-- | user_id | int | NO | MUL | NULL |
|
-- | friend_id | int | NO | MUL | NULL |
|
-- | created_at | datetime | YES | | CURRENT_TIMESTAMP |
|
-- +--------------+--------------+------+-----+-------------------+----
------------+

-- (iii) INSERT 10 USERS & FRIENDSHIPS

INSERT INTO users (username, email, dob) VALUES

('alice', 'alice@email.com', '1999-01-01'),
('bob', 'bob@email.com', '1998-02-02'),
('carol', 'carol@email.com', '1997-03-03'),
('dan', 'dan@email.com', '1996-04-04'),
('eve', 'eve@email.com', '1995-05-05'),
('frank', 'frank@email.com', '1994-06-06'),
('grace', 'grace@email.com', '1993-07-07'),
('heidi', 'heidi@email.com', '1992-08-08'),
('ivan', 'ivan@email.com', '1991-09-09'),
('judy', 'judy@email.com', '1990-10-10');
-- Output: Query OK, 10 rows affected

INSERT INTO friends (user_id, friend_id) VALUES

(1,2),(2,3),(3,4),(4,5),(5,6),(6,7),
(7,8),(8,9),(9,10),(10,1),(1,3),(2,4);
-- Output: Query OK, 12 rows affected

-- (iv) ADD 'gender' FIELD TO USERS

ALTER TABLE users ADD COLUMN gender CHAR(1) NULL;

-- Output: Query OK, 0 rows affected

-- (v) RENAME friends TABLE TO user_friends

RENAME TABLE friends TO user_friends;

-- Output: Query OK, 0 rows affected

-- (vi) RENAME dob FIELD TO date_of_birth

ALTER TABLE users CHANGE dob date_of_birth DATE;
-- Output: Query OK, 0 rows affected

-- (vii) REMOVE is_active FIELD

ALTER TABLE users DROP COLUMN is_active;

-- Output: Query OK, 0 rows affected

ALTER TABLE user_profile DROP COLUMN is_active;

-- Output: Query OK, 0 rows affected

-- (viii) DROP user_profile TABLE

DROP TABLE user_profile;

-- Output: Query OK, 0 rows affected

-- FINAL TABLE STRUCTURES

DESCRIBE users;
-- Output:
-- +---------------+--------------+------+-----+---------+-------------
---+
-- | Field | Type | Null | Key | Default | Extra
|
-- +---------------+--------------+------+-----+---------+-------------
---+
-- | user_id | int | NO | PRI | NULL |
auto_increment |
-- | username | varchar(50) | NO | | NULL |
|
-- | email | varchar(100) | NO | UNI | NULL |
|
-- | date_of_birth | date | YES | | NULL |
|
-- | gender | char(1) | YES | | NULL |
|
-- +---------------+--------------+------+-----+---------+-------------
---+

DESCRIBE user_friends;
-- Output:
-- +--------------+--------------+------+-----+-------------------+----
------------+
-- | Field | Type | Null | Key | Default |
Extra |
-- +--------------+--------------+------+-----+-------------------+----
------------+
-- | friendship_id| int | NO | PRI | NULL |
auto_increment |
-- | user_id | int | NO | MUL | NULL |
|
-- | friend_id | int | NO | MUL | NULL |
|
-- | created_at | datetime | YES | | CURRENT_TIMESTAMP |
|
-- +--------------+--------------+------+-----+-------------------+----
------------+
-- (user_profile table has been dropped, so DESCRIBE will error)
-- DESCRIBE user_profile;
-- Output: ERROR 1146 (42S02): Table 'social_networking.user_profile'
doesn't exist