Past Question Flashcards
Display all the unique categories of meals that have been ordered.
SELECT DISTINCT m.Category FROM Meals m JOIN OrderDetails od ON m.MealID = od.MealID;
Write an SQL query to calculate the average rating of all orders.
SELECT AVG(Rating) AS AverageRating FROM Ratings;
How can window functions be used to rank orders by TotalAmount for each customer?
SELECT CustomerID, OrderID, TotalAmount, RANK() OVER (PARTITION BY CustomerID ORDER BY TotalAmount DESC) AS OrderRank FROM Orders;
Differentiate between a single-key index and a composite-key index.
Single-Key: CREATE INDEX idx_customer_email ON Customers(Email); Composite-Key: CREATE INDEX idx_order_customer ON Orders(CustomerID, OrderDate);
How can the Comments column in Ratings benefit from Full-Text Search?
ALTER TABLE Ratings ADD COLUMN Comments_tsv tsvector; UPDATE Ratings SET Comments_tsv = to_tsvector(‘english’, Comments); CREATE INDEX idx_comments_fts ON Ratings USING GIN(Comments_tsv);
Write an SQL statement to insert 7 new customers into the Customer table.
INSERT INTO Customers (Name, Email, Location) VALUES (‘John Doe’, ‘john@example.com’, ‘Lagos’), (‘Jane Doe’, ‘jane@example.com’, ‘Abuja’), (‘Mike Ross’, ‘mike@example.com’, ‘Kano’), (‘Rachel Zane’, ‘rachel@example.com’, ‘Ibadan’), (‘Louis Litt’, ‘louis@example.com’, ‘Port Harcourt’), (‘Donna Paulsen’, ‘donna@example.com’, ‘Enugu’), (‘Harvey Specter’, ‘harvey@example.com’, ‘Kaduna’);
Query to find comments that contain ‘good’.
SELECT * FROM Ratings WHERE Comments ILIKE ‘%good%’;
What are Query Plans?
A Query Plan is how the database decides to execute a query. Example: EXPLAIN ANALYZE SELECT * FROM Orders WHERE OrderID = ‘1’;
Find employees who live in Abuja using Relational Algebra.
σ city=’Abuja’ (employee)
Find employees whose salary is greater than N100,000 using Relational Algebra.
σ salary > 100000 (works)
Compare a Centralized Database System with a Parallel Database System.
Centralized DB: All data is stored in a single location. Parallel DB: Data is distributed across multiple nodes for speed and efficiency.
Feature,Centralized DB,Parallel DB
Storage,One location,Multiple nodes
Speed,Slower,Faster
Fault Tolerance,Low,High
What are Bitmap index structures and how are they used for fast data retrieval?
Bitmap indexes use bitmaps to represent column values efficiently. Example: CREATE INDEX idx_gender ON Customers USING bitmap (Gender);
If a default index structure (B+ Tree) is created on the Name column, provide the current structure with a degree of 3 after the last record insertion.
The B+ Tree follows the structure with keys stored in leaf nodes and splitting occurring when full. Example: [ Bob, David, Henry ] as the root, with children nodes [ Alice ], [ Bob, Charlie ], etc.
Explain the issues with using Natural Joins and provide alternatives.
Issues: Accidental joins on unrelated columns, and dependency on column names. Alternative: Use INNER JOIN explicitly defining join conditions.
Provide an example scenario and steps for extracting a large dataset for machine learning analysis.
Scenario: Extract orders from the last 6 months. Steps: Use WHERE filter, export with COPY command, create index for faster queries.
Explain how JSON can store nutritional information for meals.
JSON allows storing flexible nutritional details without predefined columns. Example: {‘calories’: 500, ‘protein’: 30, ‘fat’: 10}.
Write a query to extract the calorie count for all meals from the JSON column NutritionalInfo.
SELECT Name, NutritionalInfo-»‘calories’ AS Calories FROM Meals;
Differentiate between json and json
Feature,JSON,JSONB
Storage,Text,Binary
Indexing,No,Yes (GIN index)
Performance,Slower,Faster
Key Order,Preserved,Not preserved
Why is json/json b semi structured
Json allows flexible key-value storage without fixed schema
{“calories”: 500, “protein”: 30}
{“fiber”: 5, “vitamins”: [“A”, “C”]}
