...
|
| Cloud MaxCompute-SQL Development (Exam) | Alibaba |
Prepare yourself for the Alibaba Cloud MaxCompute-SQL Development Exam! This certification is designed to test your expertise in managing and analyzing large-scale data with MaxCompute, one of Alibaba Cloud's leading data warehousing solutions. By passing this exam, you'll demonstrate your proficiency in SQL development and your ability to handle complex data processing tasks. Whether you're an aspiring data engineer or a seasoned professional looking to validate your skills, this exam will set you apart in the rapidly growing field of cloud computing and big data analytics. Dive into your studies and get ready to showcase your knowledge!
Notice!
Always refer to the official Alibaba Documentaion for the most accurate and up-to-date information.
Exam Question
1. Currently, MaxCompute supports specifying up to 6 small tables in a
mapjoin, otherwise syntax errors are reported, and records for a single small
table are limited to no more than 10,000.
A. True
B. False ✔
2. Which statement is incorrect for partition in MaxCompute SQL?
A. To modify values in one or more partitions among multi-level partitions, users must write values for partitions at each level.B. We cannot specify order for a new column. By default, a new column is placed in the last column.C. The name of a partition column can be modified. ✔
D. For tables that have multi-level partitions, to add a new partition, all partition values must be specified.
3. Which statement of the table life cycle is incorrect?
A. The unit of the life cycle time of a table is day.B. The data of the non partition table will be automatically recycled after setting the day number of life cycle.C. We can set the lifecycle of tables or partitions. ✔
D. The partition table determines whether the partition should be recycled according to the last modification time of each partition.
4. When you use the MapJoin, which one is incorrect?
A. The left table of ‘left outer join’ must be a big table.B. The right table of right outer join must be a big table.C. For INNER JOIN, both the left and right tables can be large tables.D. For FULL OUTER JOIN, MapJoin can be used. ✔
5. Which statement is incorrect for the dynamics partition in
MaxCompute SQL?
A. If the destination table has multi-level partitions, it is allowed to specify parts of partitions to be static partitions through ‘Insert’ statement, but the static partitions must be advanced partitions.B. The value of dynamic partition can be special characters. ✔
C. In the ‘select_statement’ field, the following field provides a dynamic partition value for the target table. If the target table has only one-level dynamic partition, the last field value of select_statement is the dynamic partition value of the target table.D. The value of dynamic partition cannot be NULL
6. Which of the following MaxCompute SQL syntax is incorrect?
A. select a.shop_name as ashop, b.shop_name as bshop from shop aright outer join sale_detail b on a.shop_name=b.shop_name.B. select a.shop_name as ashop, b.shop_name as bshop from shop afull outer join sale_detail b on a.shop_name=b.shop_name.C. SELECT * FROM table1, table2 WHERE table1.id = table2.id. xD. select a.shop_name as ashop, b.shop_name as bshop from shop a
inner join sale_detail b . ✔
7. MaxCompute SQL uses a syntax similar to SQL. Which statement is correct?
A. MaxCompute SQL extended standard SQLB. MaxCompute can be equivalent to a database.C. The maximum SQL length allowed in MaxCompute varies with the environment resources that are applied.D. MaxCompute SQL is suitable for massive data (GB, TB, EB level), off-line batch calculation scenarios. ✔
8.Which statement is incorrect for view in MaxCompute SQL?
A. To create a view, you must have ‘read’ privilege on the table referenced by view.B. Other views can be referenced by a view . Circular reference is supported. ✔
C. Views can only contain one valid ‘select’ statement.D. Writing the data into a view is not allowed, such as, using ‘insert into’ or ‘insert overwrite’ to operate view
9. Group by is for group query in MaxCompute SQL. Which of the following
query of Group by are correct?
A. Generally “group by” and aggregate function are used together.B. The key of “group by”can be the column name of the input table.C. When SELECT contains aggregate functions, “group by” can be an expression consisting of columns of input tables.D. When SELECT contains aggregate functions, “group by” can be the alias of the output column of the SELECT statement.
10. Which Join operations in MaxCompute SQL are correct?
A. MaxCompute's JOIN supports multiple links, and it also supports Cartesian product.B. Left join returns all records from the left table ✔
C. Right outer join returns all records from the right table ✔
D. Full outer join indicates the full join and returns all records from the both left and right table. ✔
11. Which of the following limitations in MaxCompute SQL are correct?
A. Table name lengthB. Table column definitionC. Table partition level and single table partition numberD. Window function numberE. Table name and field definition case
12. Which of the following queries can be executed?
A. select sum(total_price) from sale_detail group by region;B. select region as r from sale_detail group by r;C. select region, total_price from sale_detail group by region, total_price;D. select region as r from sale_detail order by region limit 100;E. select region as r from sale_detail distribute by region;
13. Which of the following descriptions about the MaxCompute SQL constraint
conditions are correct?
A. MaxCompute SQL does not support transactions. ✔
B. MaxCompute SQL does not support indexes. ✔
C. MaxCompute SQL supports delete operations.D. MaxCompute SQL does not support update operations. ✔
14. Which UNION ALL statements in MaxCompute SQL are correct ?
A. Combines two or multiple data sets returned by a SELECT operation into one data set. If the result contains duplicated rows, all rows that meet the conditions are returned, and deduplication of duplicated rows is not applied. ✔
B. MaxCompute does not support union two main query results, but you can do it on two subquery results. ✔
C. The columns of each sub query corresponding to the union all operation must be listed, not supporting *.D. The number, names, and types of queried columns corresponding to the UNION ALL/UNION operation must be consistent. ✔
15. MaxCompute SQL provides EXPLAIN operation. What does the execution
result include?
A. All resource structures corresponding to the DML sentence.B. The dependency structure of all Task corresponding to the DML sentence.C. The dependency structure of all Operator in Task.D. The dependency structure of all Task in Task
16. Suppose table t_dml only has one field named as id (type is string),
which MaxCompute SQL query cannot be executed correctly?
A. create table t_dml_bak like t_dml;B. insert into table t_dml select '1900-01-01 00:00:00' from dual; x
C. insert overwrite table t_dml select '' from dual;D. update table t_dml set id='a' ; ✔
17. Data type inconsistency is often encountered in business data
processing. In order to keep data type consistent, data processing system
involves data type conversion. If MaxComputeSQL is used for data processing,
which of the following conversion can not be achieved?
A. Bigint to StringB. String to Boolean ✔
C. String to BigintD. Datetime to String
18. MaxCompute SQL syntax does not support between conditional queries.
A. True ✔B. False
19. MaxCompute SQL supports multiple data type. Which group is incorrect?
A. Tinyint,Smallint,Int,Float,VarcharB. Bigint,String,Double,Boolean,Decimal,SmallintC. Bigint,String,Double,Datetime,Link,Decimal ✔
D. Tinyint、Smallint、 Float、Varchar、TIMESTAMP
20. During MaxCompute SQL parsing, order by/sort by/distribute by is behind
of select operation.
A. True ✔B. False
21. Which of the following statements of mapjoin Hint application of
MaxCompute SQL are correct ?
A. When a large table joins one or multiple small tables, you can use MapJoin, which performs much faster than regular JoinsB. When Mapjoin references to a small table or sub query, alias should be referenced.C. MaxCompute SQL does not support the use of complex Join conditions such as unequal expressions, or logic in normal Join's on conditions, but in MapJoin it canD. When do multiple tables Join, the two left tables can be MapJoin tables at the same time.
22. Which of the following MaxCompute SQL syntax statements are correct?
A. JOIN of MaxCompute supports n-way join, but it must be a non Cartesian product.B. The indirect expression of MaxCompute's JOIN must be an equation expression.C. When mapjoin references to a small table or sub query, alias should be referenced, otherwise it will report syntax errors.D. Right outer join returns all records in the right table, even if there is no matched record in the left table.
23. Which logical operation is incorrect in MaxCompute SQL?
A. NULL and FALSE=FALSEB. NULL and TRUE=NULL✔C. FALSE or TRUE=TRUED. TRUE or NULL=TRUE
24. The source and pattern parameters of like and rlike must be string types or integer.
A. True
B. False ✔
25. Which statement is incorrect when updating data by MaxCompute SQL?
A. When performing insert operations, the correspondence between the source
table and the target table depends on the columns order in the select
clause, not on the correspondence between the column names of the tables.
B. The value of dynamic partition cannot be NULL, but it supports special or Chinese characters.
C. Partitioned columns are not allowed to appear in the select column
list when data is inserted into a partition.
D. In the ‘select_statement’ field, the following field provides a dynamic partition value for the target table. If the target table has only one-level dynamic partition, the last field value of select_statement is the dynamic partition value of the target table.
B. The value of dynamic partition cannot be NULL, but it supports special or Chinese characters.
D. In the ‘select_statement’ field, the following field provides a dynamic partition value for the target table. If the target table has only one-level dynamic partition, the last field value of select_statement is the dynamic partition value of the target table.
26. Which of the following statements are correct ?
A. “order by” must be used together with “limit”.
B. When sorting with “order by”, NULL is considered smaller than any value. ✔
C. distribute by is to make hash slices of data according to the values of a certain columns. It is similar to group by.
D. sort by and order by are all used for sorting in essence, the difference is that the scope is not same. ✔
E. The key of order by or sort by must be the output column of the select sentences, that is, the alias of the column.
B. When sorting with “order by”, NULL is considered smaller than any value. ✔
C. distribute by is to make hash slices of data according to the values of a certain columns. It is similar to group by.
D. sort by and order by are all used for sorting in essence, the difference is that the scope is not same. ✔
E. The key of order by or sort by must be the output column of the select sentences, that is, the alias of the column.
27. Which of the following statements about order By and distribute By / sort by is incorrect in the MaxCompute SQL syntax.
A. The keys of order by/sort by/distribute by must be output columns
(namely, column aliases) of Select statements.
B. Order by or group by cannot be used together with distribute by
C. When order by is used for sorting, NULL is considered to be zero. ✔
D. Distribute by' performs hash-based sharding on data by values of certain columns. Aliases of Select output columns must be used.
B. Order by or group by cannot be used together with distribute by
C. When order by is used for sorting, NULL is considered to be zero. ✔
D. Distribute by' performs hash-based sharding on data by values of certain columns. Aliases of Select output columns must be used.
28. Which description of select in MaxCompute SQL is incorrect?
A. When using SELECT to read data from the table, specify the names of the
columns to be
read, or use an asterisk (*) to represent all columns.
B. When MaxCompute SQL does parsing, order by/sort by/distribute by are in front of SELECT. ✔
C. The WHERE clause of MaxCompute SQL supports between... And conditional query.
D. If duplicated data rows exist, you can use the Distinct option before the field to remove duplicates.In this case, only one value is returned.
read, or use an asterisk (*) to represent all columns.
B. When MaxCompute SQL does parsing, order by/sort by/distribute by are in front of SELECT. ✔
C. The WHERE clause of MaxCompute SQL supports between... And conditional query.
D. If duplicated data rows exist, you can use the Distinct option before the field to remove duplicates.In this case, only one value is returned.