Enjoy the best grades that will raise your GPA.

Category: SQL (Page 1 of 2)

Assignment 4A: Lab – Script (Schedule Date, Order Number, etc.) Create a script

Assignment
4A: Lab – Script (Schedule Date, Order Number, etc.)
Create
a script that returns complete date, Schedule date, and Order Number from
the Order table.
Create
a column that adds five days to the complete date. Call the column
‘Call Date’
Alias
the Order table
Create
a column that shows the amount of time between the schedule date and
complete date
Order
By Schedule Date
Show
your script and Results
Create
a script that returns Current Offer and charge from the Service table
Count
the number of each offer
Show
your script and results
Create
a script that pulls Course number and course name from the Course table
Only
pull courses that have the word ‘the’ in their description
Before
running the script – how many records do you think will be returned
Change
the Course number column to say ‘Course #’
Change
the Name field to say ‘Course Description’
Show
your pre-guess, script, and the results
To submit assignment, put the information above in a Word
document, including a copy of the actual SQL script required for each of the
three items. Load Word document with all content into the assignment box
for instructor review and grading.
Assignment
5A: Lab – Script (Customer, Order)
1) Create a script that pulls all the
columns for the Customer and Order tables
a) With the Customer as the primary table,
do a right join with the Order table
b) Before running the script – how many
records do you think will be returned?
c) Show your pre-guess, script, and the
results
2) Create a script that pulls Customer
first name, last name, current offer, and charge amount
a) For all the customers with a CAMP_CODE
of ‘BH’ add $.50 to their charge amount
b) For all the customers with a CAMP_CODE
of ‘CD’ add $.75 to their charge amount
c) For all the customers with a CAMP_CODE
of ‘PV’ add $1.00 to their charge amount
d) For all the customers with a CAMP_CODE
of ‘VB’ add $.25 to their charge amount
e) Create a new field called ‘Rate
Increase’ to show the new amounts.
f) Show your script and results
3) Create a script that pulls Student
First Name, Last Name, course term and name they are associated with, and any
grades they might have.
a) Concatenate the first and last name to
appear last name first and first name last with a coma between.
b) For any grades above 3.0 add .25.
For any grade below 3.0 subtract .25
c) Show your script and results
Note: I have uploaded all the document needed to run the scripts. Please also use SQLite application.

* What is implied? * Why are we talking about this in a database class? * Exampl

* What is implied?
* Why are we talking about this in a database class?
* Examples from history?
* Examples from contemporary society?
notes from class :
System is established to gain efficiency/output/input seeking efficiency
Goal: continuously
Common element: data
Development: farming, animal husbandry, specialization of labor, industrial revolution…assembly line…interne
Learning = Competitive Advantage:
Allows for specialization of labor
Emergence of experts
Innovation and process optimization
People become interchangeable resource
Organizations that “learn” will out-perform other organizations that do not
Jun 22, 2022 Lecture 2
Review: Organize or Die – mantra
Scrum is part of organize or die
Hierarchical →
Basic SQL:
Select
From
Where
Order by
Group by
SDLC:
Role of DBMS:
Back-end of N-tier Application
Holds data securely
Presents answers to queries = information
Allows for learning/gaining knowledge
History of database theory:
– File cabinet database → not learning
– Hierarchical database & Network database → complications (redundant data, less efficient system maintenance); corrupted
Biggest difference between is primary key and foreign key
– Relational data model: foreign keys. Less expensive to implement & maintain, less dependent on experts (first person articulated retinol database theory Edgar Codd)
– Easy to understand, able to improve and optimize business processes, easy to obtain information quickly
– Efficiency in data storage and implementation, scalability
No domain violation, Row dependency
Jun 23, 2022 Lecture 3
3-tier: front (presentation), middle (complex, more than 1 layer, 3 tiers by itself), back
SDLC: Systems Development Lifecycle
Planning → Analysis → Design → Build → Maintain
moF – Framework
ITIL – Framework
SDLC – Framework
Waterfall – construction
Lean – created by Toyota (Japanese) revolutionary of making cars (manufacturing)
Agile/scrum – software
ERD → Entity Relationship Diagram, conceptual, logical
Conceptual:
*interview people/research, investigate industry/business organization – existing data, observe processes
→ brainstorming
→ conceptual data diagram
Conceptual relation (row = tuple) = Logical entity (row= record) = Physical table
Column = attribute
Logical Design Phase:
Refining conceptual
Create ERD → ‘blueprint’
Normalization process
Validate transactions
pk/fk

* What is implied? * Why are we talking about this in a database class? * Exampl

* What is implied?
* Why are we talking about this in a database class?
* Examples from history?
* Examples from contemporary society?
notes from class :
System is established to gain efficiency/output/input seeking efficiency
Goal: continuously
Common element: data
Development: farming, animal husbandry, specialization of labor, industrial revolution…assembly line…interne
Learning = Competitive Advantage:
Allows for specialization of labor
Emergence of experts
Innovation and process optimization
People become interchangeable resource
Organizations that “learn” will out-perform other organizations that do not
Jun 22, 2022 Lecture 2
Review: Organize or Die – mantra
Scrum is part of organize or die
Hierarchical →
Basic SQL:
Select
From
Where
Order by
Group by
SDLC:
Role of DBMS:
Back-end of N-tier Application
Holds data securely
Presents answers to queries = information
Allows for learning/gaining knowledge
History of database theory:
– File cabinet database → not learning
– Hierarchical database & Network database → complications (redundant data, less efficient system maintenance); corrupted
Biggest difference between is primary key and foreign key
– Relational data model: foreign keys. Less expensive to implement & maintain, less dependent on experts (first person articulated retinol database theory Edgar Codd)
– Easy to understand, able to improve and optimize business processes, easy to obtain information quickly
– Efficiency in data storage and implementation, scalability
No domain violation, Row dependency
Jun 23, 2022 Lecture 3
3-tier: front (presentation), middle (complex, more than 1 layer, 3 tiers by itself), back
SDLC: Systems Development Lifecycle
Planning → Analysis → Design → Build → Maintain
moF – Framework
ITIL – Framework
SDLC – Framework
Waterfall – construction
Lean – created by Toyota (Japanese) revolutionary of making cars (manufacturing)
Agile/scrum – software
ERD → Entity Relationship Diagram, conceptual, logical
Conceptual:
*interview people/research, investigate industry/business organization – existing data, observe processes
→ brainstorming
→ conceptual data diagram
Conceptual relation (row = tuple) = Logical entity (row= record) = Physical table
Column = attribute
Logical Design Phase:
Refining conceptual
Create ERD → ‘blueprint’
Normalization process
Validate transactions
pk/fk

Directions: For each question, answer the question in a document to upload to Ca

Directions: For each question, answer the question in a document to upload to Canvas and include screenshots of the queries and results. If possible, include in your screenshot unique identifying information, such as a tab open in your browser, your name on the computer, or unique wallpaper
Neo4j
Go to: http://www.pdbmbook.com/playground (use this website !!!)
Click on Book Club database
Have a look at the nodes of the graph, using the default command:
MATCH (n)
RETURN n;
NOTE: Cypher is case-sensitive for the entities and attributes in the database (e.g., Book, Genre). It is not case-sensitive for the neo4j commands (i.e., MATCH is the same as Match). In this database, node names begin with capital letters; properties of edges are all caps, and attributes/properties of nodes are lower case.
Try this query:
MATCH (r: Reader) — (book:Book) — (genre:Genre)
WHERE genre.name = ‘humor’
RETURN r.name
Question 1: How many records does it return? Describe what this query does. Show a screenshot (5).
Question 2: Write a Cypher query to find all the people in the database who like detective books (There is a genre called ‘detective’). Show a screenshot. Describe, in words, how your query works. (10)
Question 3: Write a Cypher query to create a table that shows the name of each genre in the database along with a COUNT of the number of books of that genre. Explain your query, including whether or not an explicit grouping statement in needed. (Hint: Like SQL, in cypher you can count things(vertices or edges) — you may need to look up this command ) (15)
Question 4:
Write a cypher query that finds the genre with the most liked books by ordering books in the genre from MOST liked to LEAST liked. . You can then also ORDER BY ). Describe how your query works. (15)
Question 5: Write a query to return the names of the books that Jenny Jennifers likes. Show the query and results. State the minimum number of nodes needed in the MATCH statement. (10)
Question 6: Using A CREATE statement, add an edge between Jenny Jennifers and the book, “A Kiss too Far”. Make the attribute of the edge be: “DISLIKES” (case sensitive, all in caps). This should be a directed edge pointing from Jenny Jennifers to the book that she dislikes. (10)
Question 7: Come up with the simplest query possible to return all the books that Jenny Jennifers likes AND dislikes. Explain why the query works (15)
Question 8: Modify the query in question 7 to now return only the books that Jenny Jennifers dislikes. (10)
Question 9: Create a query that will return the names of Elvis Presley’s friends who like ‘humor’ books.
Show a screenshot of your query and what is returned . (10)

Data Definition – DDL (CREATE DATABASE, CREATE TABLE…) 1. Create the SQL stateme

Data Definition – DDL (CREATE DATABASE, CREATE TABLE…)
1. Create the SQL statements that construct (CREATE) a database named “DOCTIME” and all
of the tables (entities and attributes) using the MySQL database language syntax from the attached ERD.
2. Don’t foreget to decide the data type used for each attribute. You should use common sense
judgement when defining the data type. If the value uses any kind of text or character values, use
the VARCHAR() data type. When using the VARCHAR data type make sure you define the
maximum length potential for each value. For example: If you think the value will be on average
10 characters, may reach a maximum of 12, then use VARCHAR(12).
3. Remember, Primary Key fields require the NOT NULL property.
4. Do not worry about the Foreign Key or UNIQUE properties for this exercise.

Late days may NOT be used for this assignment. NOTE: Completion of this proposal

Late days may NOT be used for this assignment.
NOTE: Completion of this proposal requires substantial work and installation of software that might take some time. Start this early. Plan. Do not wait until the last minute!
Project Objectives:
Gain practical experience working with data management tools and real data.
Translate the concepts learned in class to actual data and to use of the data management tools, themselves
Think about data in the context of real world problems.
Communication of methods and results to a varied audience.
Learning outcomes for Part 2:
Practice working with NoSQL databases
Be able to articulate the key differences between NoSQL and relational databases in the context of real world data problems by integrating concepts learned in class
NoSQL tools: You may use either MongoDB, or Neo4J (you may feel free to use another NoSQL tool of a similar type, but to be certain the tool meets the requirements, please consult a member of the teaching team before proceeding with a different NoSQL tool). MongoDB Atlas (Links to an external site.) is the cloud version of MongoDB and you can create an account and “sandbox” to play with some small datasets for free in this service. Take the same data set you loaded into your relational databases in Part I and load this data into your NoSQL data tool of choice. You will then try to perform the same queries and see if you get the same results.
Take the paper you wrote initially and expand it now to also include both your work with the relational databases and the NoSQL data tool. You Discussion section should also now compare and contrast the NoSQL tool with the relational tool
Introduction or background. Describe your data generally. What is in the data. Why is it interesting?
Dataset: you will use. Describe the data and what is in the dataset — imagine you are describing it to someone who doesn’t know anything about the data. Describe who generated the data (if known). Such things as attributes, how many entries are in the data, and limitations or special features of the dataset would be helpful here, especially in the context of the problem areas you have said you are interested in from your background section.
Methods: Describe your NoSQL data tool. What kind of database is it? How is it different from a relational database tool? Describe how you loaded the data and describe, briefly, any challenges you had. Show some screenshots and describe your schema and show some summary statistics and/or initial rows in your data tables.
Results: Show your test queries. Were you able to pull the same results from each table? Describe any differences or anomalies that you noticed in querying the two data tools.
Discusson: Describe the differences, conceptually and practically between the two relational database tools you used and the NoSQL tool. Think about which might be better for different circumstances and discuss briefly.
Next Steps: How might this work scale to very large datasets? Which principles and features of NoSQL data tools come into play when working with very large datasets?
Collaboration report: Who was responsible for which components of the activities described in this report? It is not acceptable to say that both members contributed to all parts or to be general in the description — describe tasks as specifically as you can or show them in a bulleted list. Each individual should be responsible for specific tasks, and for full credit, there should be some combination of technical and non-technical tasks for both members even if one member did more of the technical work.

Directions: For each question, answer the question in a document to upload to Ca

Directions: For each question, answer the question in a document to upload to Canvas and include screenshots of the queries and results. If possible, include in your screenshot unique identifying information, such as a tab open in your browser, your name on the computer, or unique wallpaper
Neo4j
Go to: http://www.pdbmbook.com/playground (use this website !!!)
Click on Book Club database
Have a look at the nodes of the graph, using the default command:
MATCH (n)
RETURN n;
NOTE: Cypher is case-sensitive for the entities and attributes in the database (e.g., Book, Genre). It is not case-sensitive for the neo4j commands (i.e., MATCH is the same as Match). In this database, node names begin with capital letters; properties of edges are all caps, and attributes/properties of nodes are lower case.
Try this query:
MATCH (r: Reader) — (book:Book) — (genre:Genre)
WHERE genre.name = ‘humor’
RETURN r.name
Question 1: How many records does it return? Describe what this query does. Show a screenshot (5).
Question 2: Write a Cypher query to find all the people in the database who like detective books (There is a genre called ‘detective’). Show a screenshot. Describe, in words, how your query works. (10)
Question 3: Write a Cypher query to create a table that shows the name of each genre in the database along with a COUNT of the number of books of that genre. Explain your query, including whether or not an explicit grouping statement in needed. (Hint: Like SQL, in cypher you can count things(vertices or edges) — you may need to look up this command ) (15)
Question 4:
Write a cypher query that finds the genre with the most liked books by ordering books in the genre from MOST liked to LEAST liked. . You can then also ORDER BY ). Describe how your query works. (15)
Question 5: Write a query to return the names of the books that Jenny Jennifers likes. Show the query and results. State the minimum number of nodes needed in the MATCH statement. (10)
Question 6: Using A CREATE statement, add an edge between Jenny Jennifers and the book, “A Kiss too Far”. Make the attribute of the edge be: “DISLIKES” (case sensitive, all in caps). This should be a directed edge pointing from Jenny Jennifers to the book that she dislikes. (10)
Question 7: Come up with the simplest query possible to return all the books that Jenny Jennifers likes AND dislikes. Explain why the query works (15)
Question 8: Modify the query in question 7 to now return only the books that Jenny Jennifers dislikes. (10)
Question 9: Create a query that will return the names of Elvis Presley’s friends who like ‘humor’ books.
Show a screenshot of your query and what is returned . (10)

Why are the terms ”field” and ”record” incorrect when referring to column an

Why are the terms ”field” and ”record”
incorrect when referring to column and row
2.
Why is the term ”NULL value” incorrect
3.
How many
orders where made from the Sales.Orders table
4.
Get the
Shippeddate,the count of Numorders,the count of Shippeddate,the minimum
shippeddate as the first ship date,the maximum shippddate as the Latestshipdate
(JUST WRITE QUERY)
5.
Get the top
3 order details of Total Sales where total sales is defined as the product
of the Unitprice and quantity (JUST WRITE QUERY)
6.
Get the
Ship ID, The total freight greater than 2000 from the Sales.orders table (JUST WRITE QUERY)
7.
Get the
hire country,hiredate ,the Number of employees hired after 20030101 from the
HR.Employees Table (JUST WRITE QUERY)
8.
Write a
query to return orders that were not shipped yet. Such orders have a NULL in
the shippeddate column.
9.
You are
requested to return all orders that were placed between February 11,2008 and
February 12,2008 (JUST WRITE QUERY)
10.
If I want
to return information about employees from Washington State in the United
States, sorted by city and empid (JUST WRITE QUERY)
11.
Write a
query that returns the orders for customer 77. Such that the rows will be
sorted by shipperid. To avoid tiebreakers perform secondary ordering by shipped
date and orderid, descending respectively (JUST
WRITE QUERY)

In this course, the project assignments for each module involve learning about v

In this course, the project assignments for each module involve learning about various types of database storage. In each module, your SLP assignment will consist of completing an analysis of the database systems presented.
Option 1: Study the following:
Cprime, Inc. (2020, July 15). Power BI vs Excel: How to work with large amounts of data [Video].

Introduction to Dax
https://docs.microsoft.com/en-us/power-bi/guided-learning/introductiontodax
DAX is a very powerful tool in Excel and PowerBI that enables the analysis of very large datasets. PowerBI provides the tools to produce visualizations for the analytics on the Big Data using DAX.
Review the tutorials above, and then prepare a 3-page review of the tutorials, covering the following topics:
a comprehensive review of what the tutorials teach; choose at least two key concepts to discuss in technical detail
a thorough discussion of what you learned specifically from the tutorial
(optional, not graded but appreciated, and of benefit to you as well by studying from different sources) If you can find one or two other similar video tutorial(s) or reading material(s) on this topic, please study them and give a simple review (one or two paragraphs). Please also reference them in your reference list.

In this course, the project assignments for each module involve learning about v

In this course, the project assignments for each module involve learning about various types of database storage. In each module, your SLP assignment will consist of completing an analysis of the database systems presented.
Option 1: Study the following:
Cprime, Inc. (2020, July 15). Power BI vs Excel: How to work with large amounts of data [Video].

Introduction to Dax
https://docs.microsoft.com/en-us/power-bi/guided-learning/introductiontodax
DAX is a very powerful tool in Excel and PowerBI that enables the analysis of very large datasets. PowerBI provides the tools to produce visualizations for the analytics on the Big Data using DAX.
Review the tutorials above, and then prepare a 3-page review of the tutorials, covering the following topics:
a comprehensive review of what the tutorials teach; choose at least two key concepts to discuss in technical detail
a thorough discussion of what you learned specifically from the tutorial
(optional, not graded but appreciated, and of benefit to you as well by studying from different sources) If you can find one or two other similar video tutorial(s) or reading material(s) on this topic, please study them and give a simple review (one or two paragraphs). Please also reference them in your reference list.

« Older posts

© 2022 assignmetshive

Theme by Anders NorenUp ↑