In this we will create database for "Party Supply Store", Below is a schema for a party supply store. You will use this to create your own SQL statements to create tables, insert data, and issue queries. You are REQIRED to use MySQL to do this assignment. In the event that you are unable to get a SQL statement1 to work, you may submit what you have for partial credit.
To receive full credit, you must have both your exact SQL statement (that is, text that I can be copy and paste into MySQL) and a screenshot showing the successful executing of your statements for all steps.
The party supply store’s database schema is as follows. All attributes should not be nullable unless otherwise noted.
Item (id, name, description, color, numInStock, d_id, o_id, on_clearance, price)
CK: name
FK: d_id references Department(id)
FK: o_id references Occasion(id)
Color should be nullable
Department (id, name, description)
CK: name
Occasion (id, name, year, is_seasonal)
Creating Database Tables
Creating Department Table
CREATE TABLE Department (
ID int Primary Key Not Null,
Name varchar(45) Not Null Unique,
Description varchar(255) Not Null
);Creating Occasion Table
CREATE TABLE Occasion (
ID int Primary Key Not Null,
Name varchar(255) Not Null,
Year INT Not Null,
is_seasonal Varchar(45)
);Creating Item Table
CREATE TABLE Item (
ID int Primary Key Not Null,
Name varchar(255) Not Null,
Description varchar(255) Not Null,
Color varchar(45) Null,
NumInStock Integer Not Null,
d_id int Not Null,
o_id INT Not Null,
on_clearance varchar(255) Not Null,
Price Integer Not Null,
Foreign Key(d_id) References Department(ID),
Foreign Key(o_id) References Occasion(ID)
);Output Result
here you can see that all tables are create successfully:
Insert Data Into Tables
Next we will Inserting record into database tables:
Inserting Record into Occasion Table
INSERT INTO Occasion (id, name, year, is_seasonal) VALUES ('1', 'Black History Month', '2021', 'Yes');
INSERT INTO Occasion (id, name, year, is_seasonal) VALUES ('2', 'Valentines Day', '2021', 'Yes');
INSERT INTO Occasion (id, name, year, is_seasonal) VALUES ('3', 'Mardi Gras', '2021', 'Yes');
INSERT INTO Occasion (id, name, year, is_seasonal) VALUES ('4', 'St. Patricks Day', '2021', 'Yes');
INSERT INTO Occasion (id, name, year, is_seasonal) VALUES ('5', 'Easter', '2021', 'Yes');
INSERT INTO Occasion (id, name, year, is_seasonal) VALUES ('6', 'Cinco de Mayo', '2021', 'Yes');
INSERT INTO Occasion (id, name, year, is_seasonal) VALUES ('7', 'Graduation', '2020', 'No');
INSERT INTO Occasion (id, name, year, is_seasonal) VALUES ('8', 'Independence Day', '2021', 'Yes');
INSERT INTO Occasion (id, name, year, is_seasonal) VALUES ('9', 'Oktoberfest', '2020', 'Yes');
INSERT INTO Occasion (id, name, year, is_seasonal) VALUES ('10', 'Thanksgiving', '2020', 'Yes');
INSERT INTO Occasion (id, name, year, is_seasonal) VALUES ('11', 'Halloween', '2020', 'Yes');
INSERT INTO Occasion (id, name, year, is_seasonal) VALUES ('12', 'Day of the Dead', '2020', 'Yes');
INSERT INTO Occasion (id, name, year, is_seasonal) VALUES ('13', 'Hanukkah', '2020', 'Yes');
INSERT INTO Occasion (id, name, year, is_seasonal) VALUES ('14', 'Christmas', '2020', 'Yes');
INSERT INTO Occasion (id, name, year, is_seasonal) VALUES ('15', 'New Years Eve', '2020', 'Yes');
See Result In Workbench
Inserting Record into Department Table
INSERT INTO Department (id, name, description) VALUES ('10', 'Tableware and Serving', 'Various Table settings depending on Occasion. From low end to high end products.');
INSERT INTO Department (id, name, description) VALUES ('20', 'Balloons', 'With one of the biggest and best selections of latex and foil balloons, we offer a balloon for every taste, budget, occasion, and party theme.');
INSERT INTO Department (id, name, description) VALUES ('30', 'Decorations', 'As you explore our assortment of decorations for your party, you’ll discover paper fans, pom poms, streamers and centerpieces.');
INSERT INTO Department (id, name, description) VALUES ('40', 'Cake and Decorating Supplies', 'We have baking party supplies so you and your friends can have fun making delicious baked goods.');
INSERT INTO Department (id, name, description) VALUES ('50', 'Halloween', 'Make it easy to find everything on your Halloween shopping list. Here you’ll find exactly what you need to get ready for Halloween costume parties, trick or treat night and other fun Halloween festivities.');
INSERT INTO Department (id, name, description) VALUES ('60', 'Summer Parties', 'Throw the best summer theme party your friends have ever seen!');
INSERT INTO Department (id, name, description) VALUES ('70', 'Outdoors', 'All the accessories you need to have a great party outside, no matter what the occasion.');See Result In Workbench
Inserting Record into Item Table
INSERT INTO Item (id, name, description, color, NumInStock, d_id, o_id, on_clearance, price) VALUES ('919984', 'Black Extra Sturdy Paper Dinner Plates 10in 24ct', 'Serve Your Guests In Style with these Black Plates', 'Black', '1000', '10', '1', 'No', '3.99');
INSERT INTO Item (id, name, description, color, NumInStock, d_id, o_id, on_clearance, price) VALUES ('113860', 'Black Premium Plastic Cutlery Set 24ct', 'Give your guests a premium dining experience! These plastic utensils are washable and reusable for all of your black-themed special occasions!', 'Black', '1000', '10', '1', 'No', '2.99');
INSERT INTO Item (id, name, description, color, NumInStock, d_id, o_id, on_clearance, price) VALUES ('928809', 'Air-Filled Gold I Am Black History Balloon Phrase, 13in, 15pc', 'Gold Colored Balloon Set that would make a great centerpiece for any Black History Month Party.', 'Gold', '500', '20', '1', 'No', '24.99');
INSERT INTO Item (id, name, description, color, NumInStock, d_id, o_id, on_clearance, price) VALUES ('466603', 'Black Paper Lanterns 3ct', 'Create a whimsical party atmosphere with our Black Paper Lanterns! These festive paper lanterns arrive flat but easily expand into round decorations with the included metal frames.', 'Black', '500', '30', '1', 'No', '6.99');
INSERT INTO Item (id, name, description, color, NumInStock, d_id, o_id, on_clearance, price) VALUES ('928660', 'Inclusion is the Future Yard Sign', 'State the facts loud and proud with an Inclusion is the Future yard sign. The outdoor sign features an "Inclusion Is The Future" headline and hands in a variety of skin tones raised in unison.', 'Multi', '250', '70', '1', 'No', '12.99');
INSERT INTO Item (id, name, description, color, NumInStock, d_id, o_id, on_clearance, price) VALUES ('607600', 'Bold Valentines Day Combo Pack for 30', 'Set a lovely scene with a Bold Valentines Day Combo Pack for 30! This matching set of paper tableware features pink and red hearts surrounding a "Happy Valentines Day" message on a clean white background', 'Multi', '1000', '10', '2', 'No', '5.99');
INSERT INTO Item (id, name, description, color, NumInStock, d_id, o_id, on_clearance, price) VALUES ('926681', 'Red Cupid Heart Valentines Day Foil Balloon Bouquet, 13pc', 'A Heart Balloon Bouquet comprised of Plain Red Heart Balloons, Happy Valentines Day Heart Balloons and at the center a Cupid with Heart Balloon.', 'Red', '500', '20', '2', 'No', '24.99');
INSERT INTO Item (id, name, description, color, NumInStock, d_id, o_id, on_clearance, price) VALUES ('460658', 'Red Swirl Decorations 12ct', 'Whimsical swirls make every occasion more festive! Red Swirl Decorations are durable plastic, with attached plastic hooks for easy display and removal. Combine tight spirals with loose swirls for a multidimensional party look!', 'Red', '500', '30', '2', 'No', '5.99');
INSERT INTO Item (id, name, description, color, NumInStock, d_id, o_id, on_clearance, price) VALUES ('609062', 'Red Cupcake Stand', 'A Red Cupcake Stand provides a bold touch to any party table! This red, three-tiered cupcake stand comes with scallop-edged borders for a stylish look. This Red Cupcake Stand is perfect for cupcakes, candy, cookies, and other delicious treats!', 'Red', '750', '40', '2', 'No', '7.99');
INSERT INTO Item (id, name, description, color, NumInStock, d_id, o_id, on_clearance, price) VALUES ('184991', 'Wilton Jumbo Heart Sprinkles', 'Dress up your desserts with a sprinkle of love. Guests will love seeing your desserts decorated with these big heart sprinkles.', 'Multi', '500', '40', '2', 'No', '4.99');
INSERT INTO Item (id, name, description, color, NumInStock, d_id, o_id, on_clearance, price) VALUES ('811918', 'Good Times Mardi Gras Lunch Napkins 125ct', 'Keep a stack of Good Times Mardi Gras Lunch Napkins on the table at your Mardi Gras party to avoid messes! These black paper napkins feature a gold, green, and purple "Let the Good Times Roll!" headline with matching designs.', 'Multi', '500', '10', '3', 'No', '8.99');
INSERT INTO Item (id, name, description, color, NumInStock, d_id, o_id, on_clearance, price) VALUES ('811914', 'Good Times Mardi Gras Balloons 72ct', 'Create unique balloon bouquets for your Mardi Gras celebration with Good Times Mardi Gras Balloons! These latex balloons include solid gold, green, and purple, plus the same colors with "Mardi Gras!" headlines and Mardi Gras-themed designs.', 'Multi', '500', '20', '3', 'No', '9.99');
INSERT INTO Item (id, name, description, color, NumInStock, d_id, o_id, on_clearance, price) VALUES ('811931', 'Mardi Gras Ornament Wreath', 'Get ready for your Mardi Gras festivities by decorating your home with a Mardi Gras Ornament Wreath! The decoration features gold, green, and purple ornaments and gold tinsel.', 'Multi', '500', '30', '3', 'No', '24.99');
INSERT INTO Item (id, name, description, color, NumInStock, d_id, o_id, on_clearance, price) VALUES ('185132', 'Foil Spray Mardi Gras Centerpiece', 'Fire off a sparkling display with a Mardi Gras Foil Spray Centerpiece! This bright foil centerpiece features green, purple, and yellow theater mask cutouts in an explosion of shiny metallic strips.', 'Multi', '500', '30', '3', 'No', '4.99');
INSERT INTO Item (id, name, description, color, NumInStock, d_id, o_id, on_clearance, price) VALUES ('137583', 'Mardi Gras Cupcake Liners with Picks', 'Jazz up cupcakes with these Big-Easy-inspired Mardi Gras Cupcake Liners with Picks. Your cupcakes will be the king and queen of the dessert table when dressed up in these purple, green and yellow liners.', 'Multi', '500', '40', '3', 'No', '3.99');
INSERT INTO Item (id, name, description, color, NumInStock, d_id, o_id, on_clearance, price) VALUES ('927255', 'Deluxe Mardi Gras Float House Outdoor Decoration Kit', 'Like the Name states this could be used to decorate a float in the Mardi Gras Parade or simply just decorate the outside of your home for your Mardi Gras Party.', 'Multi', '500', '70', '3', 'No', '99.99');
INSERT INTO Item (id, name, description, color, NumInStock, d_id, o_id, on_clearance, price) VALUES ('780388', 'Festive Green Plastic Tableware Kit for 50 Guests', 'Perfect Festive Green Kit to use for any St. Patrick’s Day Party. Comes with everything you need including cups, cutlery, plates, napkins, and table runners.', 'Green', '250', '10', '4', 'No', '33.99');
INSERT INTO Item (id, name, description, color, NumInStock, d_id, o_id, on_clearance, price) VALUES ('243630', 'Festive Green Balloons 15ct, 12in', 'Decorate your party space in bold color! Our Festive Green Balloons feature a glossy finish, perfect for a variety of occasions.', 'Green', '1000', '20', '4', 'No', '2.99');
INSERT INTO Item (id, name, description, color, NumInStock, d_id, o_id, on_clearance, price) VALUES ('495918', 'Shamrock Spray Centerpiece', 'Whether you are serving traditional corned beef and cabbage or something non-traditional, a Shamrock Spray Centerpiece will add a dose of Irish luck to your table! Green foil sprays and wires are adorned with golden shamrocks.', 'Green', '500', '30', '4', 'No', '1.49');
INSERT INTO Item (id, name, description, color, NumInStock, d_id, o_id, on_clearance, price) VALUES ('608854', 'St. Patricks Day Shamrock Cupcake Decorating Kit', 'They will be after your lucky treats when they are decorated using this Shamrock Cupcake Decorating Kit! St. Patricks Day cupcakes look better in these green printed paper cupcake liners with clover shamrock prints.', 'Green', '500', '40', '4', 'No', '2.99');
INSERT INTO Item (id, name, description, color, NumInStock, d_id, o_id, on_clearance, price) VALUES ('730745', 'Friendly Standing Leprechaun Decoration', 'Invite a friendly leprechaun to share in your St. Patricks Day festivities! The Friendly Standing Leprechaun Decoration is wearing a green top hat with buckle, green suit, and black shoes. His face has rosy cheeks, an orange beard, and a sweet smile.', 'Green', '1000', '70', '4', 'No', '5.99');
INSERT INTO Item (id, name, description, color, NumInStock, d_id, o_id, on_clearance, price) VALUES ('863694', 'Tulip Garden Table Cover', 'Set the foundation for a springtime meal with a table cover covered in fresh blooms. The borders of this table protector feature busy bees flying around a garden of tulips, lavender, and dandelions.', 'White', '500', '10', '5', 'No', '2.99');
INSERT INTO Item (id, name, description, color, NumInStock, d_id, o_id, on_clearance, price) VALUES ('862839', 'Giant Ombre Stacked Easter Egg Balloon, 35in', 'A stacked Easter egg balloon will make an eye-catching addition to your Easter egg hunt or picnic. This balloon resembles three stacked Easter eggs in rainbow pastel colors.', 'Multi', '1000', '20', '5', 'No', '9.99');
INSERT INTO Item (id, name, description, color, NumInStock, d_id, o_id, on_clearance, price) VALUES ('866078', 'Hello Bunny Boys & Girls Easter Swirl Decorations 12ct', 'These bouncy cardstock decorations feature spring bunnies and chickees with fun messages like "Chicks Rule" and "Hoppy Easter.”', 'Multi', '500', '30', '5', 'No', '5.99');
INSERT INTO Item (id, name, description, color, NumInStock, d_id, o_id, on_clearance, price) VALUES ('905665', 'Hoppy Easter Bunny Cake Decorating in a Box', 'If you love baking you will love this Easter Bunny Cake Decorating Kit in a Box. Bake your cake in the shape of a bunny head, and then decorate it however you like.', 'None', '250', '40', '5', 'No', '17.99');
INSERT INTO Item (id, name, description, color, NumInStock, d_id, o_id, on_clearance, price) VALUES ('910061', 'Easter Bunny Fence Topper', 'The Easter bunny has popped up to add some fun to your outdoor holiday decorations! The fence topper hangs from any gate or fence and looks like a peeking white Easter rabbit with pink polka dot ears and a burgundy polka dot bow tie.', 'White', '500', '70', '5', 'No', '14.99');
INSERT INTO Item (id, name, description, color, NumInStock, d_id, o_id, on_clearance, price) VALUES ('850260', 'Serape Tableware Kit for 32 Guests', 'A very colourful tableware kit for your own Cinco de Mayo Party', 'Multi', '500', '10', '6', 'No', '53.99');
INSERT INTO Item (id, name, description, color, NumInStock, d_id, o_id, on_clearance, price) VALUES ('825826', 'Fiesta Time Balloons 15ct', 'Create a unique decoration for your fiesta using Fiesta Time Balloons! The blue, red, and yellow latex balloon feature headlines like "Fiesta Time" "Ole!" and "Taco Bout A Party!"', 'Multi', '500', '20', '6', 'No', '3.99');
INSERT INTO Item (id, name, description, color, NumInStock, d_id, o_id, on_clearance, price) VALUES ('428742', 'Inflatable Cactus Ring Toss Cooler', 'Cool down during your fiesta while having fun using this Inflatable Cactus Ring Toss Cooler! This inflatable beverage cooler doubles as a party game. Comes with rings to toss and try and land around the cactus pieces', 'Multi', '250', '30', '6', 'No', '19.99');
INSERT INTO Item (id, name, description, color, NumInStock, d_id, o_id, on_clearance, price) VALUES ('867288', 'Custom Fiesta Fun Yard Sign', 'Decorate your lawn to direct guests to the party with a custom Fiesta Fun Yard Sign! The colorful sign features a "Fiesta" headline, maracas, and space to add a personalized message.', 'Multi', '250', '70', '6', 'No', '12.99');
INSERT INTO Item (id, name, description, color, NumInStock, d_id, o_id, on_clearance, price) VALUES ('700362', 'Graduation Grand Tableware Kit', 'This is the perfect gift for a large gathering Graduation Party. Comes with all the tableware accessories that you need to celebrate your graduate.', 'Multi', '150', '10', '7', 'No', '109.99');
INSERT INTO Item (id, name, description, color, NumInStock, d_id, o_id, on_clearance, price) VALUES ('778893', 'Blue Congrats Grad Foil Balloon Bouquet, 5pc', 'Surprise the graduate on their special day by giving them a Blue Congrats Grad Balloon Bouquet!', 'Blue', '500', '20', '7', 'No', '19.99');
INSERT INTO Item (id, name, description, color, NumInStock, d_id, o_id, on_clearance, price) VALUES ('911383', 'Multicolor Congrats Grad Spray Centerpiece, 18in', 'This grand centerpiece features black foil tinsel spray; blue, green, and red wire strands of mortarboards and stars; and shining pom poms that make a big impression.', 'Multi', '500', '30', '7', 'No', '4.99');
INSERT INTO Item (id, name, description, color, NumInStock, d_id, o_id, on_clearance, price) VALUES ('905667', 'Colorful Commencement Cupcake Display in a Box', 'A baking kit to celebrate your graduate. Includes a cupcake stand, cupcake wrappers, and decorating kit.', 'Multi', '250', '40', '7', 'No', '24.99');
INSERT INTO Item (id, name, description, color, NumInStock, d_id, o_id, on_clearance, price) VALUES ('859171', 'Custom Black, Gold & Silver Graduation Photo Yard Sign', 'Decorate your lawn to direct guests to the graduation party using a Custom Black, Gold & Silver Graduation Photo Yard Sign! This graduation sign is made of sturdy corrugated plastic and can be placed in your lawn using the included stake.', 'Multi', '250', '70', '7', 'No', '12.99');
INSERT INTO Item (id, name, description, color, NumInStock, d_id, o_id, on_clearance, price) VALUES ('912072', 'Painted Patriotic American Flag Paper Lunch Plates, 8.5in, 50ct', 'These patriotic plates feature a brush stroke-style American Flag design. With 50 plates to spare, there are plenty to go around whether you are having a large celebration or a more intimate gathering.', 'Multi', '500', '10', '8', 'No', '7.99');
INSERT INTO Item (id, name, description, color, NumInStock, d_id, o_id, on_clearance, price) VALUES ('237870', 'Red Balloons 15ct, 12in', 'A party essential, these helium quality Red Latex Balloons inflate to 12 inches high. What better way to celebrate Independence Day than with these beautiful Red Balloons.', 'Red', '1000', '20', '8', 'No', '2.99');
INSERT INTO Item (id, name, description, color, NumInStock, d_id, o_id, on_clearance, price) VALUES ('453730', 'Patriotic Cascade Centerpiece', 'Bring all-American pride to the table with a Patriotic Cascade Centerpiece! Featuring red, silver, and blue metallic sprays and shiny foil strands tipped with die-cut stars, this patriotic centerpiece stands on a clear plastic base when fully assembled.', 'Multi', '500', '30', '8', 'No', '2.99');
INSERT INTO Item (id, name, description, color, NumInStock, d_id, o_id, on_clearance, price) VALUES ('559720', 'Patriotic American Flag Baking Cups 75ct', 'What better way to celebrate the land of the free and the home of the brave than with your favorite desserts wrapped in Old Glory?', 'Multi', '500', '40', '8', 'No', '2.99');
INSERT INTO Item (id, name, description, color, NumInStock, d_id, o_id, on_clearance, price) VALUES ('923616', '4th of July Patriotic Potato Sack Race Bags/USA Flag, Patriotic Head Boppers Headband/Red White Blue, Prize Medals for Kids and Adults Patriotic Outdoor Fun Games, Fourth of July Party Favors', '4th of July Burlap Bags are Wonderful Gifts for a Patriotic Party. The USA Flag and Red White Blue Headband are designed for Fourth of July and Bring More Fun to your Kids and Their Friends at Patriotic Party.', 'Multi', '250', '60', '8', 'No', '30.99');
INSERT INTO Item (id, name, description, color, NumInStock, d_id, o_id, on_clearance, price) VALUES ('425768', 'Oktoberfest Lunch Plates 8ct', 'Set the table for an Oktoberfest celebration. These Oktoberfest plates feature a bier stein and pretzels with festive headline and Bavarian checkered border. Keep a stack by the buffet table at your Oktoberfest party.', 'Multi', '1000', '10', '9', 'No', '2.69');
INSERT INTO Item (id, name, description, color, NumInStock, d_id, o_id, on_clearance, price) VALUES ('237865', 'Royal Blue Balloons 15ct, 12in', 'A party essential, Royal Blue Latex Balloons inflate to 12 inches in diameter. Blue balloons will definitely add that Oktoberfest vibe to any party.', 'Blue', '1000', '20', '9', 'No', '2.99');
INSERT INTO Item (id, name, description, color, NumInStock, d_id, o_id, on_clearance, price) VALUES ('425734', 'Oktoberfest Swirl Decorations 5ct', 'Dandy danglers for your Biergarten! These Oktoberfest Swirl Decorations feature two-sided beer stein cut outs attached to blue foil swirls that catch light. Hang them above your table or from the trees to finish off your Oktoberfest decorations.', 'Multi', '500', '30', '9', 'No', '4.99');
INSERT INTO Item (id, name, description, color, NumInStock, d_id, o_id, on_clearance, price) VALUES ('074860', 'Oktoberfest - Dessert Cupcake Toppers - German Beer Festival Clear Treat Picks - Set of 24', 'Oktoberfest Treat Picks make it easy to style your party and add that extra touch of detail that your guests will love. The Oktoberfest Treat Pick Kit includes 3 different German beer designs with a total of 24 shaped cut outs.', 'None', '500', '40', '9', 'No', '13.99');
INSERT INTO Item (id, name, description, color, NumInStock, d_id, o_id, on_clearance, price) VALUES ('880676', 'Custom Oktoberfest Yard Sign', 'Direct guests to the party with a custom Oktoberfest yard sign. The colorful sign features pretzel and beer stein icons, a background featuring a Bavarian flag pattern, and space to add a personalized message.', 'Multi', '250', '70', '9', 'No', '12.99');
INSERT INTO Item (id, name, description, color, NumInStock, d_id, o_id, on_clearance, price) VALUES ('845205', 'Fall Foliage Vinyl Table Cover', 'Decorate your fall buffet with this Fall Foliage Vinyl Table Cover. This vinyl table cover is beige with orange, yellow, green, and red printed throughout. This reusable vinyl table cover makes clean-up a breeze!', 'Multi', '1000', '10', '10', 'No', '7.99');
INSERT INTO Item (id, name, description, color, NumInStock, d_id, o_id, on_clearance, price) VALUES ('794580', 'White & Gold Happy Thanksgiving Balloon, 17in', 'Add decorative shine to your Thanksgiving dinner by decorating it with a White & Gold Happy Thanksgiving Balloon! The round white foil balloon features a gold "Happy Thanksgiving" headline bordered by rose gold vines.', 'Multi', '500', '20', '10', 'No', '2.99');
INSERT INTO Item (id, name, description, color, NumInStock, d_id, o_id, on_clearance, price) VALUES ('796942', 'Rustic Turkey Decoration', 'Set a Rustic Turkey Decoration on the table to complete your Thanksgiving dinner decorations! The foam and paper turkey features feathers that look like pinecones, straw, and wood.', 'Multi', '125', '30', '10', 'No', '16.99');
INSERT INTO Item (id, name, description, color, NumInStock, d_id, o_id, on_clearance, price) VALUES ('824915', 'Thanksgiving/Fall Cupcake Supplies for 75 - Baking Cups/Wrappers, Autumn Leaves Edible Decorating Sprinkles Mix, Wooden Pick Toppers, Great Thanksgiving Decorations for Muffins', 'Celebrate thanksgiving in style with these Thanksgiving / Fall cupcake baking supplies set, it includes all the items you need for creating stylish and delicious Thanksgiving / Fall themed cupcakes.', 'Orange', '500', '40', '10', 'No', '22.00');
INSERT INTO Item (id, name, description, color, NumInStock, d_id, o_id, on_clearance, price) VALUES ('598040', 'Fall Leaves String Lights', 'Celebrate the coming of fall with a set of Fall Leaves String Lights! These decorative patio lights feature woven brown plastic wires made to look like branches.', 'Multi', '250', '70', '10', 'No', '9.99');
INSERT INTO Item (id, name, description, color, NumInStock, d_id, o_id, on_clearance, price) VALUES ('807170', 'Black & Orange Plastic Tableware Kit for 50 Guests', 'Perfect Simple Tableware Kit for any Halloween Party. Everything comes in Black or Orange which are classic Halloween Colors.', 'Multi', '500', '50', '11', 'No', '33.99');
INSERT INTO Item (id, name, description, color, NumInStock, d_id, o_id, on_clearance, price) VALUES ('921583', 'Smiley Pumpkin Halloween Foil Balloon Bouquet, 5pc', 'These five packs of balloons features a cheerful jack-o-lantern trio, "Happy Halloween" pumpkin balloons full of colorful stars, and two solid black star balloons to tie it all together.', 'Multi', '500', '50', '11', 'No', '20.00');
INSERT INTO Item (id, name, description, color, NumInStock, d_id, o_id, on_clearance, price) VALUES ('922797', 'Classic Black Bats Woven Fabric Mantel Scarf, 6ft x 2.3ft', 'Your home can look eerie and elegant with a classic black mantel scarf. The woven decoration looks like an open spiderweb with bats on the borders and scattered throughout.', 'Black', '250', '50', '11', 'No', '9.99');
INSERT INTO Item (id, name, description, color, NumInStock, d_id, o_id, on_clearance, price) VALUES ('843886', 'Graveyard Cupcake Decorating Kit', 'The dead will rise with hunger but not before we do! Transform yummy cupcakes into little graveyard plots with Wilton Graveyard Cupcake Decorations.', 'White', '275', '50', '11', 'No', '8.99');
INSERT INTO Item (id, name, description, color, NumInStock, d_id, o_id, on_clearance, price) VALUES ('914589', 'Graveyard Skeleton Halloween Yard Decorating Kit', 'For the Halloween Enthusiast, this is a very reasonable Halloween Decorating Kit. Includes 2 RIP Headstones, 2 Skull Fences, a full-size skeleton and skeleton arms.', 'Multi', '175', '50', '11', 'No', '74.99');
INSERT INTO Item (id, name, description, color, NumInStock, d_id, o_id, on_clearance, price) VALUES ('843616', 'Sugar Skull Dinner Plates 18ct', 'Add some color to your Dia de los Muertos party tables with the Sugar Skull Dinner Plates! These black plates feature a sugar skull with colorful floral designs and has white stars and purple line curls in the background.', 'Multi', '500', '10', '12', 'No', '5.99');
INSERT INTO Item (id, name, description, color, NumInStock, d_id, o_id, on_clearance, price) VALUES ('749665', 'Day of the Dead Sugar Skull Balloon, 24in', 'Add some color to your Dia de los Muertos fiesta with this Day of the Dead Sugar Skull Balloon! This foil balloon is shaped like a sugar skull with colorful designs of flowers, hearts, and more.', 'Multi', '500', '20', '12', 'No', '9.99');
INSERT INTO Item (id, name, description, color, NumInStock, d_id, o_id, on_clearance, price) VALUES ('627226', 'Black Paper Candelabra - Haunted House', 'This Haunted House Black Paper Candelabra keeps things dark and dreary for your haunted house! In jet black paper, this ornate candelabra decoration features sinister skull cutouts, plus black gauze that drapes around for a cobweb effect.', 'Black', '250', '30', '12', 'No', '8.99');
INSERT INTO Item (id, name, description, color, NumInStock, d_id, o_id, on_clearance, price) VALUES ('752894', 'Sugar Skull Cookie Cutter Stamper', 'Use our Day of the Dead sugar skull cookie cutter stamper to cut out cookies. Cookie stampers not only cut out the shape, they also imprint a fun designs on the dough by pressing down on the plunger on the back of the cutter.', 'Purple', '500', '40', '12', 'No', '4.99');
INSERT INTO Item (id, name, description, color, NumInStock, d_id, o_id, on_clearance, price) VALUES ('770172', 'Custom Day of the Dead Yard Sign', 'Decorate your lawn to direct guests to the Dia de los Muertos celebration with a custom Sugar Skull Yard Sign. The outdoor decoration features a trio of sugar skulls, a border of swirls and stars, and space to add a personalized message.', 'Black', '500', '70', '12', 'No', '12.99');
INSERT INTO Item (id, name, description, color, NumInStock, d_id, o_id, on_clearance, price) VALUES ('740068', 'Joyous Holiday Passover Table Cover', 'Display all of your delicious Passover Seder dishes on an elegant Joyous Holiday Passover Table Cover! This plastic table cover features blue edges with silver and white leaf and Star of David designs.', 'Multi', '250', '10', '13', 'No', '2.99');
INSERT INTO Item (id, name, description, color, NumInStock, d_id, o_id, on_clearance, price) VALUES ('853219', 'Festival of Lights Hanukkah Balloon, 17in', 'Decorate your space for eight nights of fun with a Festival of Lights Hanukkah Balloon. The round foil balloon features a menorah illustration on a blue background made of Stars of David.', 'Multi', '250', '20', '13', 'No', '2.99');
INSERT INTO Item (id, name, description, color, NumInStock, d_id, o_id, on_clearance, price) VALUES ('637327', 'Hanukkah Spray Centerpiece', 'Bring some sparkle to your table with a Hanukkah Spray Centerpiece. This festive Hanukkah centerpiece features metallic blue and light blue strands with attached Stars of David.', 'Blue', '250', '30', '13', 'No', '4.99');
INSERT INTO Item (id, name, description, color, NumInStock, d_id, o_id, on_clearance, price) VALUES ('609070', 'Royal Blue Baking Cups 75ct', 'These beautiful deep blue cupcake cups go very well with Hanukkah as blue is one of the main colors used in Hanukkah decorations. They can be used for anything baking for your Hanukkah celebration.', 'Blue', '500', '40', '13', 'No', '1.99');
INSERT INTO Item (id, name, description, color, NumInStock, d_id, o_id, on_clearance, price) VALUES ('881049', 'Custom Hanukkah Celebration Yard Sign', 'Direct guests to the party with a custom Hanukkah yard sign. The blue and gold sign features a golden dreidel, a fully-lit menorah, a "Happy Hanukkah" headline, and space for a personalized message.', 'Multi', '250', '70', '13', 'No', '12.99');
INSERT INTO Item (id, name, description, color, NumInStock, d_id, o_id, on_clearance, price) VALUES ('694924', 'Winter Wonder Snowman Table Cover', 'The weather outside is frightful but your table looks so delightful topped with a Winter Wonder Snowman Table Cover. This plastic Christmas table cover features a rustic Christmas theme.', 'Multi', '1000', '10', '14', 'No', '2.99');
INSERT INTO Item (id, name, description, color, NumInStock, d_id, o_id, on_clearance, price) VALUES ('806214', 'Iridescent Snowflake Balloon Bouquet 5pc', 'It will be a white Christmas when you decorate your home with an Iridescent Snowflake Balloon Bouquet! The balloon bouquet includes a large foil balloon featuring iridescent, gold, and silver snowflakes.', 'Multi', '500', '20', '14', 'No', '19.99');
INSERT INTO Item (id, name, description, color, NumInStock, d_id, o_id, on_clearance, price) VALUES ('490078', 'Snowflake Fan Decorations 3ct', 'Add a winter touch to your holiday decor using Snowflake Paper Fan Decorations! A lighter snow for your holiday display, these paper fan decorations unfold into dazzling cutout snowflakes, graceful and airy with delicate 3D shapes.', 'White', '500', '30', '14', 'No', '5.99');
INSERT INTO Item (id, name, description, color, NumInStock, d_id, o_id, on_clearance, price) VALUES ('854370', 'Wilton Snowflake Icing Decorations 24ct', 'Add a dash of winter flair to your baking with Wilton Snowflake Icing Decorations! The sugary snowflake-shaped dessert toppers come in shades of blue and a variety of sized.', 'Blue', '750', '40', '14', 'No', '2.99');
INSERT INTO Item (id, name, description, color, NumInStock, d_id, o_id, on_clearance, price) VALUES ('166869', '9-foot Pre-lit Spruce Garland with LED Lights, Cones, Berries, and Glitter – Green', 'This festive Holiday spruce garland is decorated with silver tone bristles, cones, red berries and glitter. Pre-lit with 50 warm white battery-operated LED lights, decorate your home or office in seasonal style with this inviting 108-inch garland.', 'Green', '500', '70', '14', 'No', '37.99');
INSERT INTO Item (id, name, description, color, NumInStock, d_id, o_id, on_clearance, price) VALUES ('805027', 'Metallic Silver Cheers Champagne Flutes 8ct', 'Raise Metallic Silver Cheers Champagne Flutes in the air with everyone at your New Years Eve party! The clear plastic champagne flutes feature metallic silver "Cheers!" headlines and metallic silver stems.', 'None', '500', '10', '15', 'No', '9.99');
INSERT INTO Item (id, name, description, color, NumInStock, d_id, o_id, on_clearance, price) VALUES ('455460', 'Black, Clear, Gold & Silver Happy New Year Balloons 15ct', 'Start off the New Year on the glam foot! Our Black, Clear, Gold & Silver Happy New Year Balloons feature a printed "Happy New Year" headline with dots and stars. These latex NYE balloons are must-haves for your countdown party with a glitzy theme!', 'Multi', '1000', '20', '15', 'No', '4.99');
INSERT INTO Item (id, name, description, color, NumInStock, d_id, o_id, on_clearance, price) VALUES ('804963', 'New Years Eve Scene Setter', 'Friends and family can take candid pictures at your New Years party when you decorate with a New Years Eve Scene Setter! The scene setter includes a black poster with gold, silver, and white New Years Eve-themed designs and headlines.', 'Multi', '500', '30', '15', 'No', '12.99');
INSERT INTO Item (id, name, description, color, NumInStock, d_id, o_id, on_clearance, price) VALUES ('881520', 'Elegant Standard Baking Cup', 'Elegant and classic standard cupcakes papers in black and gold swirl. Perfect for any part of your New Year’s Celebration.', 'Multi', '500', '40', '15', 'No', '23.65');
INSERT INTO Item (id, name, description, color, NumInStock, d_id, o_id, on_clearance, price) VALUES ('1733678', 'New Years Glitter Door Mural', 'This decorative accent is as iconic as it is beautiful. This fabric door covers will transform any door into a piece of artwork.', 'Multi', '500', '70', '15', 'No', '62.99');See Result In Workbench
Implementing Queries Over Database
Query 1
1. List all the information stored in the occasion table.
Select * From Occasion;Result
Query 2
List the id and name for all departments that begin with a letter H.
Select id, name From Department where Name like 'H%';Result
Query 3
List all the item information for all items with no color specified.
Select * From Item where Color = 'None';Result
Query 4
Show all occasions that are from 2020 or are not seasonal.
Select * From Occasion where Year='2020' or is_seasonal='No';Result
Query 5
For each item that costs more than $9.99 show the name of the item, its description, the name of its department, and its cost.
Select I.Name as 'Name', I.Description as 'Description', I.Price as 'Price', D.Name as 'Department' from Item I, Department D where I.d_id=D.id and I.Price>9.99;Result
Query 6
Update the Item tables so that all items associated with occasions with the year of 2020 are now on clearance. (Hint: You will need to use an update statement.)
UPDATE Item LEFT JOIN Occasion ON Item.o_id = Occasion.id SET Item.on_clearance ='Yes' WHERE Occasion.Year=2020;Result
Query 7
Update the Item tables so that all items on clearance are now 10% less. (Hint: You will need to use an update statement and remember that you can do arithmetic in an update statement.)
Update Item Set Price = Price*0.9 Where on_clearance= ‘Yes’;Query 8
For each occasion, show the names and total quantity of items in stock.
Select I.Name as 'Name', I.NumInStock as 'Quantity in Stock', O.Name as 'Occasion' from Item I, Occasion O where I.o_id=O.id;Result
Send your request at realcode4you@gmail.com and get instant help with an affordable price.
We are always focus to delivered unique or without plagiarism code which is written by our highly educated professional which provide well structured code within your given time frame.
If you are looking other programming language help like C, C++, Java, Python, PHP, Asp.Net, NodeJs, ReactJs, etc. with the different types of databases like MySQL, MongoDB, SQL Server, Oracle, etc. then also contact us.