Data Management - Applications - D427

Data Management - Applications - D427 Leave the first rating Students also studied Terms in this set (22) Western Governors UniversityD 426 Save WGU - D427 - Data Management - ...66 terms helen_robertson4 Preview WGU - D427 Data Management - Ap...27 terms helen_robertson4 Preview C839v5/D334 Additional Practice Teacher 165 terms ShawnD_Preview Data M 107 term jed Create a data type with a positive valueUNSIGNED Designating a foreign key in CREATE TABLE statement FOREIGN KEY (ColumnName) REFERENCES Tablename(ColumnName), Command to add a column to an existing table ALTER TABLE TableName ADD COLUMN ColumnName DATATYPE; SQL statement to create a view name Myview that contains X, Y, Z columns from the Maintable table.CREATE VIEW Myview AS

SELECT X, Y, Z

FROM Maintable; SQL statement to delete the view named Myview DROP VIEW Myview; SQL statement to modify the Test table to make the ID column the primary key ALTER TABLE Test

ADD PRIMARY KEY (ID);

Write a SQL statement to designate the Year column in the Movie table as a foreign key to the Year column in the YearStats table.ALTER TABLE Movie ADD FOREIGN KEY (Year) REFERENCES YearStats(Year); Write a SQL statement to create an index named idx_year on the Year column of the Movie table.CREATE INDEX idx_year ON Movie (Year); SQL statement to add a new row to a tableINSERT INTO Table (column1, column2,...) VALUES (value1, value2,...);

SQL statement to delete a row with ID value of 295 from Test table DELETE FROM Test

WHERE ID = 295;

Statement used to add, delete or modify existing columns. Also used to add or drop constraints ALTER TABLE Statement that is used to modify the existing records in a table.UPDATE Write a SQL query to output the unique RatingCode values and the number of movies with each rating value from the Movie table as RatingCodeCount. Sort the results by the RatingCode in alphabetical order A–Z.Ensure your result set returns the columns in the order indicated.SELECT DISTINCT RatingCode, COUNT(Title) AS RatingCodeCount FROM Movie GROUP BY RatingCode ORDER BY RatingCode; Write a SQL query to display both the Title and the TotalGross (if available) for all movies. Ensure your result set returns the columns in the order indicated.SELECT Title, TotalGross FROM Movie LEFT JOIN YearStats ON Movie.Year = YearStats.Year; Write a SQL query to return how many movies have a Year value of 2019.SELECT COUNT(Year) FROM Movie WHERE Year = "2019"; Statement to delete column named Oldcolumn from table Test ALTER TABLE Test DROP Oldcolumn; Statement to add a column named Newcolumn with INT datatype to table Test ALTER TABLE Test ADD COLUMN Newcolumn INT; Statement to rename a column OldColumn to Newcolumn and change the datatype to INT in the Test table ALTER TABLE Test CHANGE Oldcolumn Newcolumn INT; Syntax to set a value in a column named Column to null SET Column = NULL SQL code to delete rows from TestDELETE FROM Test a constraint used to limit values of a column before a change is committed to the database CHECK Example syntax to use a CHECK constraint to add a row named Height with 3 significant digits and 1 decimal place that must be >= 10.0 and <=20.0 while creating a table named Horse CREATE TABLE Horse( Height DECIMAL(3,1) CHECK (Height BETWEEN 10.0 and 20.0) );