Book Store Indexes

--Book Store Indexes.md

Book Store Indexes

We are running an online bookstore and need to keep track of what books we offer. We’ll be working with a database of three tables. The books table is created from the top selling books of all time. The customers and orders tables are randomly generated.

As a note, this project will most likely run slower for you than most other projects in Codecademy. In order to see the benefits of an index, you need a large database. We tried to make the database as small as possible but still large enough to get a benefit of creating good indexes.

script.sql

/* 1 */
SELECT * FROM customers LIMIT 10;
SELECT * FROM orders LIMIT 10;
SELECT * FROM books  LIMIT 10;

/* 2 */
SELECT * FROM pg_indexes WHERE tablename = 'customers';
SELECT * FROM pg_indexes WHERE tablename = 'orders';
SELECT * FROM pg_indexes WHERE tablename = 'books';

/* 3 */
CREATE INDEX idx_orders_customer_id ON orders (customer_id);
CREATE INDEX idx_orders_book_id ON orders (book_id);

/* 4 */
EXPLAIN ANALYZE SELECT original_language, title, sales_in_millions 
FROM books 
WHERE original_language = 'French';

/* 5 */
SELECT pg_size_pretty (pg_total_relation_size('books'));

/* 6 */
CREATE INDEX idx_books_language_title_sales 
ON books (original_language, title, sales_in_millions);

/* 7 */
/* 
Add "EXPLAIN ANALYZE" in front of the query to analyze and compare the runtime. 
*/

/* 8 */
DROP INDEX idx_books_language_title_sales;

/* 10 */
DROP INDEX IF EXISTS idx_orders_customer_id;
DROP INDEX IF EXISTS idx_orders_book_id;

/* 9 */
SELECT NOW();
 
\COPY orders FROM 'orders_add.txt' DELIMITER ',' CSV HEADER;
 
SELECT NOW();

/* 10 */
CREATE INDEX idx_orders_customer_id ON orders (customer_id);
CREATE INDEX idx_orders_book_id ON orders (book_id);

/* 11 */
CREATE INDEX idx_customers_first_name_email ON customers (first_name, email_address);

The SUSE SCA_SLES15 Exam Dumps by DumpsCafe are the perfect study resource for IT professionals preparing for the SUSE Certified Administrator (SCA) in Enterprise Linux 15 certification. DumpsCafe provides updated exam questions, PDF dumps, practice tests, and a powerful testing engine that closely reflects the real exam environment, helping candidates master system administration, file systems, networking, storage, and user management on SLES 15. With detailed explanations and real-exam-oriented practice questions, DumpsCafe ensures effective exam preparation in less time. Take advantage of the New Year Sale with a massive 70% discount and enjoy instant access through a smooth add-to-cart checkout process, making DumpsCafe the trusted choice for passing the SCA_SLES15 certification exam on the first attempt. https://www.dumpscafe.com/Braindumps-sca_sles15.html