TECHUB HW1-5 QUESTIONS

pdf

School

New York University *

*We aren’t endorsed by this school

Course

Subject

Business

Date

Apr 3, 2024

Type

pdf

Pages

Uploaded by MinisterPencil10242

Assignment # 1 1.PLEASE FILL OUT THE CLASS SURVEY TO GET FULL CREDIT FOR THIS WEEK’S HW: h:ps://forms.gle/z3oeNTcYYeBMR54y6 2.What enabled the digital revoluXon? 3. How do digital and analog compare in terms of copying informaXon? Which provides a more reliable copy? 4. What is the difference between a CIO and a CTO?

Assignment # 2 SCM: Q1: What is the relaXonship between CAD and CAM systems? Q2: What is the difference between EOQ and BOM? Q3: What is RFID, and what role does it play in SCM? Hardware: Q1: You decide to buy parts and build your own computer. At the minimum, what are the components that you would need for this device to be considered a computer

Q2: When a computer is offered for sale, one of its adverXsed characterisXcs is something such as “2.9 GHz.” What does this mean, and what does it measure? Q3: If a computer’s clock rate is 1Ghz and the capacity is 32 bits, what is the throughput? Q4: A startup of 100 employees wants to have a central computer to run its socware off of. It does not need to send much data back and forth, and only has one locaXon. What kind of a computer is this startup likely to use, a supercomputer, mainframe computer, server, personal computer or workstaXon? Q5: If a company needs a storage soluXon that has the fastest recording and retrieval Xme along with high storage capacity, what storage soluXon should the company use? Zara Case: Q1: How would you advise Salgado to proceed on the issue of upgrading Zara’s POS systems? Should the company: - Upgrade the POS terminals to a modern operaXng system?

Your preview ends here

Eager to read complete document? Join bartleby learn and gain access to the full version

Access to all documents
Unlimited textbook solutions
24/7 expert homework help

- Build in-store networks? - Give employees the ability to look up inventory balances for items in their own stores? - Give employee the ability to look up inventory balances for items in other stores? Q2: What was Zara’s business model in 2003? How was it different from other retailers at that Xme?

Q3: What informaXon does Zara need to operate its business model? Q4: What do you think of Zara’s approach to IT in 2003? What current or potenXal strengths and weaknesses do you see in Zara’s approach?

Assignment #3 Socware: Q1: What are some of the advantages of higher-level programming languages? Q2: What is the difference between system socware and applicaXon socware? Q3: What is the difference between a compiler and interpreter? Q4: What is open source socware? To what does the word “source” refer to? Google Sheets: Go online and do it

Your preview ends here

Eager to read complete document? Join bartleby learn and gain access to the full version

Access to all documents
Unlimited textbook solutions
24/7 expert homework help

Assignment #4 Databases: Q1: What is the difference between a database and a database management system? Q2: What are the advantages and disadvantages of object-oriented databases? Q3: What is SQL? In which database model does it operate? Why is it so popular? Q4: What is a data warehouse? How is it different from a transacXonal or relaXonal database? Q5: What are the phases of adding data to a data warehouse?

SQL Filtering: IMPORTANT INSTRUCTIONS: For each query below, please copy and paste your queries into the word document and answer the quesXon. For example, if the quesXon asks “How many products do we have?” Your answer should state: “109 products: SELECT * FROM products”. You do NOT need to submit screenshots of your queries, just copy and paste your query into a word document and save it as a PDF. Q1. What unique statuses do we have for sales orders? Q2. How many sales transacXons were canceled? Create a list. Q3. How many transacXons were canceled that are over $3000 in sales? Create a list. Q4. How many transacXons over $3000 are for items that cost between 80 and 100? Create a list. Q5. How many transacXons were canceled or on hold for orders over $3000 and items that cost between 80 and 100? Create a list. Q6. How many unique orders were canceled that had sales over $3000 on any transacXon? Create a list.

Assignment #5 Math & Aggregate FuncXons: 1. Calculate the sale price per piece for each item in the sales table and name the column sale_price. 2. Calculate the profit per piece (and name the column profit_per_piece) by first calculaXng the price per piece that each item was sold at and the price it cost. Order the sales table from the most profitable item per piece. 3. How many suppliers do we have that have all four cerXficaXons (CPC, GCC, ICTI & DOC)? 6 suppliers have all four cerXficaXons (CPC, GCC, ICTI & DOC): 4. How many small, medium and large sales do we have for each category? 5. What is the average msrp by product line (round to nearest hundredths)? 6. What is the average msrp by product line and product supplier id? Order the list by product line and then by average msrp (highest to lowest). 7. What is the total profit by salesperson id? Order the list from highest profit to lowest profit. SQL JOINS: 1. Our buying team wants to know what products are made at what factories. Create a table that provides this informaXon

Your preview ends here

Eager to read complete document? Join bartleby learn and gain access to the full version

Access to all documents
Unlimited textbook solutions
24/7 expert homework help

2. What suppliers are not making any products? 3. A customer asks for a list of all products made at factories with an ICTI cerXficaXon. How many products do we sell that are made in ICTI cerXfied factories? 4. What is the average msrp by manufacturer (create a table of supplier and the average msrp and alphabeXze the list by supplier name)? 5. What are the Top 5 countries by the number of products in our line that they produce? 6. What is the total sales by sales oﬃce (NY & SF)? 7. What salespeople have not sold anything? Weather.com Case: 1. Now that public weather datasets were becoming available, how would TWC be able to maintain its compeXXve advantage with its weather-centric running app? How exclusive was the weather content?

2. How could TWC conXnue to leverage mobile apps to further moneXze its significant investment in its weather data? What addiXonal mobile use cases should they pursue? Why? 3. Should The Weather Company expend its efforts developing consumer apps, or should it focus on its professional services division, offering weather and biometeorological analyXcs to its business customers? Could they effecXvely do both?

Queries SELECT * FROM sales LIMIT 200; SELECT supplier_id, supplier AS supplier_name, country FROM suppliers; SELECT code, line, name AS product_name FROM products; SELECT * FROM salespeople ORDER BY oﬃce, fname DESC; SELECT * FROM sales ORDER BY sales DESC; -- This allows write whatever and wont affect code --DESC: SORTS HIGHEST to lowest put acer column name /* allows you to comment out mulXple lines*/ SELECT * FROM products; SELECT DISTINCT line, supplier_id FROM products ORDER BY line, supplier_id; SELECT DISTINCT country FROM customers; SELECT DISTINCT country, city FROM customers ORDER BY country, city; SELECT * FROM sales WHERE sales > 4000 AND price >=90; SELECT * FROM sales WHERE status = 'Shipped'; SELECT * FROM sales WHERE status <> 'Shipped'; SELECT * FROM sales WHERE status != 'Shipped'; SELECT * FROM sales WHERE status ILIKE '%Ship%'; SELECT * FROM sales WHERE status NOT ILIKE '%Ship%'; --like searches for exact string of ship, ilike ignore capitalizaXon -- What products do we have from supplier 106, 108, 109, use OR because (and only searches for x AND y) or searches for (x or y or z) SELECT * FROM products WHERE supplier_id = '106' OR supplier_id = '108' OR supplier_id = '109'; SELECT * FROM products WHERE supplier_id IN ('106', '108','109'); --what products are not SELECT * FROM products WHERE supplier_id != '106' OR supplier_id != '108' OR supplier_id != '109'; SELECT * FROM products WHERE supplier_id NOT IN ('106', '108','109');

Your preview ends here

Eager to read complete document? Join bartleby learn and gain access to the full version

Access to all documents
Unlimited textbook solutions
24/7 expert homework help

SELECT * FROM sales WHERE sales >= 1000 AND sales <=5000; SELECT * FROM sales WHERE sales BETWEEN 1000 AND 5000; SELECT * FROM sales WHERE deal_size = 'small' AND price <50; SELECT * FROM products WHERE name ILIKE '%Ferrari%'; SELECT * FROM sales WHERE salesperson IN ('1','2,','4') ORDER BY sales; --quesXon 3,4,5 -- What sales do we have for salesperson 1 that are above 4000? Order from highest to lowest? SELECT * FROM sales WHERE salesperson = '1' AND sales > 4000 ORDER BY sales DESC; -- Calculate profit for each item in the sales table and label this as profit SELECT *, (qty*price) AS total_cost, sales-(qty*price) AS profit FROM sales; --column in end^ L6 is column in beginning SELECT qty*price AS total_cost FROM,* sales; --customr 17 gets 5% disc off each invoice, what is the total profit? SELECT *, (qty*price) AS total_cost, ROUND((0.95*sales)-(qty*price),2) AS profit FROM sales WHERE customer_no = '17'; --when ordes cancelled, nobody wants, sell to liquidator at 80% of cost you paid. create column called discounted_sales for goods that are cancelled. the discounted_sales price should be 80% of the cost of the item SELECT *, (qty*price) AS total_cost, (qty*price)*0.8 AS discounted_sales FROM sales WHERE status ILIKE'%Cancelled%'; --Loss on goods SELECT *, (qty*price) AS total_cost, (qty*price)*0.8 AS discounted_sales, 0.2*qty*price AS loss FROM sales WHERE status ILIKE'%Cancelled%'; SELECT COUNT (code) FROM products; --total sales in history (max, sum, avg must be numeric) SELECT SUM(sales) FROM sales; -- highest sale f(x) SELECT MAX(sales) FROM sales; -- total sales in history of company by product SELECT product_code, SUM(sales) FROM sales GROUP BY product_code ORDER BY SUM(sales) DESC; --total sales in history by sales person and product (order by in order, so sales and then prod. if product first then it shows product most sold then total sales) SELECT salesperson, product_code, SUM(sales) AS total_sales

FROM sales GROUP BY salesperson, product_code ORDER BY salesperson, sum(sales) DESC; --highest sale by sales person order high to low SELECT salesperson, MAX(sales) AS highest_sale FROM sales GROUP BY salesperson ORDER by MAX(sales) DESC; --avg msrp by supplier high to low SELECT supplier_id, AVG(msrp) AS avg_msrp FROM products GROUP BY supplier_id ORDER BY AVG(msrp) DESC; --how many products have we sold a total of 100,000 or more? order highest to low -->s12 code -->top 5 SELECT product_code, SUM(sales) AS total_sales FROM sales WHERE product_code ILIKE '%S12%' GROUP BY product_code HAVING SUM(sales) > 100000 ORDER BY SUM(sales) DESC LIMIT 5; -- create list of prod sold more than 25 order by no. Xmes each product was sold SELECT product_code, COUNT(*) AS transacXons FROM sales GROUP BY product_code HAVING COUNT(*) > 25 ORDER BY COUNT(*) DESC; --How many suppliers do we have in each county that have a GCC cerXficaXon? Order the list from highest to lowest GCC cerXfied suppliers in each country. SELECT country, COUNT(*) from suppliers WHERE gcc = 'Y' GROUP BY country ORDER BY COUNT(*) DESC; --sales for NY oﬃce SELECT * FROM sales a INNER JOIN salespeople b ON a.salesperson = b.id; -- what customers not bought from us SELECT * FROM customers a LEFT JOIN sales b ON a.cust_id = b.customer_no WHERE b.sales IS NULL; -- what products and product lines were sold SELECT * FROM products a INNER JOIN sales b ON a.code = b.product_code; -- are there any products in our list that we have not sold? SELECT * FROM products a LEFT JOIN sales b ON a.code = b.product_code WHERE b.sales IS NULL; -- total sales by salesperson, include first name and last name

SELECT b.fname, b.lname, SUM(a.sales) FROM sales a INNER JOIN salespeople b ON a.salesperson = b.id GROUP BY b.fname, b.lname ORDER BY sum(a.sales) DESC; -- what are the total sales by product line, order table from highest to lowest SELECT b.line, SUM(a.sales) FROM sales a INNER JOIN products b ON a.product_code = b.code GROUP BY b.line ORDER BY SUM(a.sales) DESC; Hw4 SELECT DISTINCT status FROM sales; SELECT * FROM sales WHERE status = 'Cancelled'; SELECT * FROM sales WHERE status = 'Cancelled' AND sales > 3000; SELECT * FROM sales WHERE sales > 3000 AND price BETWEEN 80 AND 100; SELECT * FROM sales WHERE (status = 'Cancelled' OR status ='On Hold') AND sales > 3000 AND price BETWEEN 80 AND 100; SELECT DISTINCT order_num FROM sales WHERE status = 'Cancelled' AND sales > 3000; Hw5 --Calculate the sale price per piece for each item in the sales table and name the column sale_price. SELECT *, (sales/qty) AS sale_price FROM sales; --Calculate the profit per piece (and name the column profit_per_piece) by first calculaXng the price per piece that each item was sold at and the price it cost. Order the sales table from the most profitable item per piece. SELECT *, (sales/qty) AS sale_price, ((sales/qty)-price) AS profit_per_piece FROM sales ORDER BY ((sales/qty)-price) DESC; --How many suppliers do we have that have all four cerXficaXons (CPC, GCC, ICTI & DOC)? SELECT COUNT(*) FROM suppliers WHERE cpc ILIKE 'Y' AND gcc ILIKE 'Y' AND icX ILIKE 'Y' AND doc ILIKE 'y'; --How many small, medium and large sales do we have for each category? SELECT deal_size, COUNT(*) FROM sales GROUP BY deal_size; --What is the average msrp by product line (round to nearest hundredths) SELECT line, ROUND (AVG(msrp),2) AS avg_msrp FROM products GROUP BY line; --What is the average msrp by product line and product supplier id? Order the list by product line and then by average msrp (highest to lowest). SELECT line, supplier_id, AVG(msrp) AS avg_msrp FROM products GROUP BY line, supplier_id ORDER BY line, AVG(msrp) DESC; --What is the total profit by salesperson id? Order the list from highest profit to lowest profit.

Your preview ends here

Eager to read complete document? Join bartleby learn and gain access to the full version

Access to all documents
Unlimited textbook solutions
24/7 expert homework help

SELECT salesperson, SUM(sales-(price*qty)) AS profit FROM sales GROUP BY salesperson ORDER BY profit DESC; --Our buying team wants to know what products are made at what factories. Create a table that provides this informaXon. SELECT * FROM products a INNER JOIN suppliers b ON a.supplier_id = b.supplier_id; --What suppliers are not making any products? SELECT * FROM suppliers a LEFT JOIN products b ON a.supplier_id = b.supplier_id WHERE b.code IS NULL; --A customer asks for a list of all products made at factories with an ICTI cerXficaXon. How many products do we sell that are made in ICTI cerXfied factories? SELECT * FROM products a INNER JOIN suppliers b ON a.supplier_id = b.supplier_id WHERE b.icX ILIKE 'Y'; --What is the average msrp by manufacturer (create a table of supplier and the average msrp and alphabeXze the list by supplier name)? SELECT b.supplier, AVG(a.msrp) FROM products a INNER JOIN suppliers b ON a.supplier_id = b.supplier_id GROUP BY b.supplier_id ORDER BY b.supplier_id; --What are the Top 5 countries by the number of products in our line that they produce? SELECT b.country, COUNT(*) FROM products a INNER JOIN suppliers b ON a.supplier_id = b.supplier_id GROUP BY b.country ORDER BY COUNT(*) DESC LIMIT 5; --What is the total sales by sales oﬃce (NY & SF)? SELECT b.oﬃce, SUM(a.sales) FROM sales a INNER JOIN salespeople b ON a.salesperson = b.id GROUP BY b.oﬃce; --What salespeople have not sold anything? SELECT * FROM salespeople a LEFT JOIN sales b ON a.id = b.salesperson WHERE b.sales IS NULL;

TECHUB HW1-5 QUESTIONS

Related Documents