#oracle sql interview questions for freshers

[eswars-tech-world]

Question-100: Explain the concept of Oracle Application Express (APEX) and its features for rapid web application development.

For more questions do follow the Main page Please share the content and let’s help others to skill-up #oracledatabase #interviewquestions #freshers #beginners #intermediatelevel #experienced #eswarstechworld #oracle #interview #development #sql

Oracle Application Express (APEX) is a web-based rapid application development framework provided by Oracle. Oracle Application Express (APEX) is a low-code development platform that allows developers to create robust and scalable web applications quickly. It is tightly integrated with the Oracle Database and provides a declarative development environment, enabling developers to build…

[sgraphinfotechbangalore-blog]

Oracle SQL Interview Questions and Answers

Q1.) What is the difference between DML and DDL? Ans: DDL (Data Definition Language) is used mainly for defining the database schema. It consists of SQL statements which give an idea about the structure of the database. Some examples of DDL statements are: CREATE: Used to create new database objects ALTER: Used to alter characteristics of already existing database objects DROP: Used to remove database objects TRUNCATE: Used to remove all records including all spaces allocated to record from the table RENAME: Used to rename any database object DML (Data Manipulation Language) are responsible for managing and manipulating various data schemas or database objects. They cannot alter the characteristics of a database object unlike DDL statements. Most common examples are: SELECT: Used to retrieve data from the database INSERT: Used to insert new data/records into the database UPDATE: Used to update/modify the existing data/record from the database DELETE: Used to remove the existing data/record from the database Q2.) What does NVL function does? Ans: NVL function is mainly used to replace NULL values with some other value provided the data type of the NULL value to be replaced with the given value is same. Q3.) What is a BLOB data type? Ans: BLOB is a binary string of varying length used to store data up to 2 gigabytes. Images, videos and other documents can be stored in the database using BLOB data type. Q4.) What is the difference between varchar and varchar2? Ans: The data type varchar is used to stored data up to 2000 bytes while varchar2 is used to store data up to 4000 bytes. However, the prominent difference between varchar and varchar2 is that varchar can occupy NULL values while varchar2 cannot. Q5.) What are the different Oracle SQL database objects? Ans: Tables - Database object used to store various database records     Views - It is a virtual table derived from one or more tables     Indexes - Used to improve efficiency and performance of the database     Synonyms - Aliases used for database tables Q6.) What are various types of integrity constraints in Oracle SQL? Ans: Unique: It ensures that the given column or entity is unique and cannot be duplicated. Not NULL: It ensures that the given column cannot be empty. Foreign: It ensures that the two entities/tables share a common key with the one in parent table being a primary key and the one in child table being a foreign key. Q7.) What is a hash cluster? Ans: It is used to store table in a manner that query fetching results in faster data retrieval by using the hashing technique. Q8.) What is a sub query? Ans: Sub query refers to a query written with in a query. It is often referred to as nested query or inner query. It is used to apply filters on selected data retrieved by the main query. Q9.) What is an alert? Ans: A window appearing in the center of the screen overlaying the current screen to provide some form of information is termed as an alert. Q10.) Which is the fastest query method used to fetch data? Ans: Since Oracle uses clustering index technique, fetching records using ROWID is the fastest and the most efficient query method.If you looking for Oracle SQL Training in Bangalore, Sgraph Infotech is the best and top training center in marathahalli in Bangalore.

http://www.sgraphinfotech.com/blog/oracle-sql-interview-questions-and-answers.html

[brieflyprofoundsandwich]

The Way to Locate Sql Technical Interview Questions Online Make certain you understand every question. If you put in the interview room backed by a few years of DBMS Administration experience you're going to be asked questions in your own real life encounter. Evidently, questions vary greatly because of position you are recruiting for. Below you will find some general questions to find more information regarding candidate's SQL abilities. It's a simple fact that the questions may be answered in many of ways that are potential. Almost invariably, if you're a fresher, you're likely to be asked issues, or else you are a Database Administrator. You should answer some questions concerning SSRS, to start in the company. https://tinyurl.com/y2emjnh4 Continue reading and you'll find an answer. The answer should be one. The issue is you need to express the solution in your words in order for your abilities and imagination will come out at front of the interviewers. What Does Sql Technical Interview Questions Mean? When we encounter a problem the first thing we must do would be to realize the matter. The issue is the SQL Standard asserts that we can't elect for a column which is not part of the group by clause unless it contained inside an aggregate function. https://annuaire-du-net.com/sql-interview-questions-and-answers/ The issue here is that we do not know what will be returned because we're not with what we are requesting in the SQL specific enough! As soon as you've known what the matter is, another thing to do is to write down the issue in words and also the sources of input. Brush up as much as you are able to, if your expertise is a bit mild. My experience is that I get a fantastic deal more if I've finished a legwork that is tiny to begin with to what's being taught, to find some exposure. Knowledge gained through conference sessions is a source of expertise that is high quality, but direct experience is always the best thing. What the corporation will need from you would be to be able to utilize that knowledge to mine the information pertinent to your analysis from the database, although an comprehension of SQL commands is outstanding. First you want the database abilities and then you require the meeting abilities. The skills for the database might be obtained on the job. You might be asked to spell out the reasons you left the occupation. Jobs may be implemented on demand. So If you're looking for work that is connected to SSIS, you have to prepare yourself for the 2019 SSIS interview queries. PSRELEASE you might have observed there are lesser skilled folks, who manage to find the tasks easily and you wind up in the receiving end. Interviews are an excellent way to assess abilities . You dress up 16, if you are going for an in person interview afterward ensure. Interviews contain a couple of concerns. An interview enables the candidate to estimate needs of the job and the corporate culture. For those professionals which are looking forward to attending Oracle Database Administrator interview in times, below are a few. Your interviewer is trying to observe the way your character will contribute to a positive, open work culture. The interviewer would love to know you discover that it's possible when things are not straightforward to be resourceful and flexible. She or he wants to know what you are searching for which you did not find in your prior occupation. She or he wishes to know that you're capable of handling situations, and that you can resolve problems yourself. Your interviewer will probably need to look at your knowledge to find out whether you will name the orders required for programmers. Coding evaluations may be used in two ways. In order to display accurate, they need to be the ones that are ideal. Online coding tests are sometimes a procedure. annuaire-du-net sql developer interview questions There are some SQL tests available on the market but often they verify the academic knowledge instead of abilities and programming skills of candidate to deal with real life difficulties. http://bit.ly/2jYDMm8 Things You Won't Like About Sql Technical Interview Questions and Matters You Will Information can be stored only in 1 order on disc. Data control language (DCL) is a sort of syntax that's comparable to a computer programming language. There are and it is determined by the association between tables.

[yodalearningweb-blog]

Business Analyst Finance Domain Sample Resume

This is just a sample Business Analyst resume for freshers as well as for experienced job seekers in Finance domain of business analyst or system analyst. While this is only a sample resume, please use this only for reference purpose, do not copy the same client names or job duties for your own purpose. Always make your own resume with genuine experience.

Name: Justin Megha

Ph no: XXXXXXX

your email here.

Business Analyst, Business Systems Analyst

SUMMARY

Accomplished in Business Analysis, System Analysis, Quality Analysis and Project Management with extensive experience in business products, operations and Information Technology on the capital markets space specializing in Finance such as Trading, Fixed Income, Equities, Bonds, Derivatives(Swaps, Options, etc) and Mortgage with sound knowledge of broad range of financial instruments. Over 11+ Years of proven track record as value-adding, delivery-loaded project hardened professional with hands-on expertise spanning in System Analysis, Architecting Financial applications, Data warehousing, Data Migrations, Data Processing, ERP applications, SOX Implementation and Process Compliance Projects. Accomplishments in analysis of large-scale business systems, Project Charters, Business Requirement Documents, Business Overview Documents, Authoring Narrative Use Cases, Functional Specifications, and Technical Specifications, data warehousing, reporting and testing plans. Expertise in creating UML based Modelling views like Activity/ Use Case/Data Flow/Business Flow /Navigational Flow/Wire Frame diagrams using Rational Products & MS Visio. Proficient as long time liaison between business and technology with competence in Full Life Cycle of System (SLC) development with Waterfall, Agile, RUP methodology, IT Auditing and SOX Concepts as well as broad cross-functional experiences leveraging multiple frameworks. Extensively worked with the On-site and Off-shore Quality Assurance Groups by assisting the QA team to perform Black Box /GUI testing/ Functionality /Regression /System /Unit/Stress /Performance/ UAT's. Facilitated change management across entire process from project conceptualization to testing through project delivery, Software Development & Implementation Management in diverse business & technical environments, with demonstrated leadership abilities. EDUCATION

Post Graduate Diploma (in Business Administration), USA Master's Degree (in Computer Applications), Bachelor's Degree (in Commerce), TECHNICAL SKILLS

Documentation Tools UML, MS Office (Word, Excel, Power Point, Project), MS Visio, Erwin

SDLC Methodologies Waterfall, Iterative, Rational Unified Process (RUP), Spiral, Agile

Modeling Tools UML, MS Visio, Erwin, Power Designer, Metastrom Provision

Reporting Tools Business Objects X IR2, Crystal Reports, MS Office Suite

QA Tools Quality Center, Test Director, Win Runner, Load Runner, QTP, Rational Requisite Pro, Bugzilla, Clear Quest

Languages Java, VB, SQL, HTML, XML, UML, ASP, JSP

Databases & OS MS SQL Server, Oracle 10g, DB2, MS Access on Windows XP / 2000, Unix

Version Control Rational Clear Case, Visual Source Safe

PROFESSIONAL EXPERIENCE

SERVICE MASTER, Memphis, TN June 08 - Till Date

Senior Business Analyst

Terminix has approximately 800 customer service agents that reside in our branches in addition to approximately 150 agents in a centralized call center in Memphis, TN. Terminix customer service agents receive approximately 25 million calls from customers each year. Many of these customer's questions are not answered or their problems are not resolved on the first call. Currently these agents use an AS/400 based custom developed system called Mission to answer customer inquiries into branches and the Customer Communication Center. Mission - Terminix's operation system - provides functionality for sales, field service (routing & scheduling, work order management), accounts receivable, and payroll. This system is designed modularly and is difficult to navigate for customer service agents needing to assist the customer quickly and knowledgeably. The amount of effort and time needed to train a customer service representative using the Mission system is high. This combined with low agent and customer retention is costly.

Customer Service Console enables Customer Service Associates to provide consistent, enhanced service experience, support to the Customers across the Organization. CSC is aimed at providing easy navigation, easy learning process, reduced call time and first call resolution.

Responsibilities

Assisted in creating Project Plan, Road Map. Designed Requirements Planning and Management document. Performed Enterprise Analysis and actively participated in buying Tool Licenses. Identified subject-matter experts and drove the requirements gathering process through approval of the documents that convey their needs to management, developers, and quality assurance team. Performed technical project consultation, initiation, collection and documentation of client business and functional requirements, solution alternatives, functional design, testing and implementation support. Requirements Elicitation, Analysis, Communication, and Validation according to Six Sigma Standards. Captured Business Process Flows and Reengineered Process to achieve maximum outputs. Captured As-Is Process, designed TO-BE Process and performed Gap Analysis Developed and updated functional use cases and conducted business process modeling (PROVISION) to explain business requirements to development and QA teams. Created Business Requirements Documents, Functional and Software Requirements Specification Documents. Performed Requirements Elicitation through Use Cases, one to one meetings, Affinity Exercises, SIPOC's. Gathered and documented Use Cases, Business Rules, created and maintained Requirements/Test Traceability Matrices. Client: The Dun & Bradstreet Corporation, Parsippany, NJ May' 2007 - Oct' 2007

Profile: Sr. Financial Business Analyst/ Systems Analyst.

Project Profile (1): D&B is the world's leading source of commercial information and insight on businesses. The Point of Arrival Project and the Data Maintenance (DM) Project are the future applications of the company that the company would transit into, providing an effective method & efficient report generation system for D&B's clients to be able purchase reports about companies they are trying to do business.

Project Profile (2): The overall purpose of this project was building a Self Awareness System(SAS) for the business community for buying SAS products and a Payment system was built for SAS. The system would provide certain combination of products (reports) for Self Monitoring report as a foundation for managing a company's credit.

Responsibilities:

Conducted GAP Analysis and documented the current state and future state, after understanding the Vision from the Business Group and the Technology Group. Conducted interviews with Process Owners, Administrators and Functional Heads to gather audit-related information and facilitated meetings to explain the impacts and effects of SOX compliance. Played an active and lead role in gathering, analyzing and documenting the Business Requirements, the business rules and Technical Requirements from the Business Group and the Technological Group. Co - Authored and prepared Graphical depictions of Narrative Use Cases, created UML Models such as Use Case Diagrams, Activity Diagrams and Flow Diagrams using MS Visio throughout the Agile methodology Documented the Business Requirement Document to get a better understanding of client's business processes of both the projects using the Agile methodology. Facilitating JRP and JAD sessions, brain storming sessions with the Business Group and the Technology Group. Documented the Requirement traceability matrix (RTM) and conducted UML Modelling such as creating Activity Diagrams, Flow Diagrams using MS Visio. Analysed test data to detect significant findings and recommended corrective measures Co-Managed the Change Control process for the entire project as a whole by facilitating group meetings, one-on-one interview sessions and email correspondence with work stream owners to discuss the impact of Change Request on the project. Worked with the Project Lead in setting realistic project expectations and in evaluating the impact of changes on the organization and plans accordingly and conducted project related presentations. Co-oordinated with the off shore QA Team members to explain and develop the Test Plans, Test cases, Test and Evaluation strategy and methods for unit testing, functional testing and usability testing Environment: Windows XP/2000, SOX, Sharepoint, SQL, MS Visio, Oracle, MS Office Suite, Mercury ITG, Mercury Quality Center, XML, XHTML, Java, J2EE.

GATEWAY COMPUTERS, Irvine, CA, Jan 06 - Mar 07

Business Analyst

At Gateway, a Leading Computer, Laptop and Accessory Manufacturer, was involved in two projects,

Order Capture Application: Objective of this Project is to Develop Various Mediums of Sales with a Centralized Catalog. This project involves wide exposure towards Requirement Analysis, Creating, Executing and Maintaining of Test plans and Test Cases. Mentored and trained staff about Tech Guide & Company Standards; Gateway reporting system: was developed with Business Objects running against Oracle data warehouse with Sales, Inventory, and HR Data Marts. This DW serves the different needs of Sales Personnel and Management. Involved in the development of it utilized Full Client reports and Web Intelligence to deliver analytics to the Contract Administration group and Pricing groups. Reporting data mart included Wholesaler Sales, Contract Sales and Rebates data.

Responsibilities:

Product Manager for Enterprise Level Order Entry Systems - Phone, B2B, Gateway.com and Cataloging System. Modeled the Sales Order Entry process to eliminate bottleneck process steps using ERWIN. Adhered and practiced RUP for implementing software development life cycle. Gathered Requirements from different sources like Stakeholders, Documentation, Corporate Goals, Existing Systems, and Subject Matter Experts by conducting Workshops, Interviews, Use Cases, Prototypes, Reading Documents, Market Analysis, Observations Created Functional Requirement Specification documents - which include UMLUse case diagrams, Scenarios, activity, work Flow diagrams and data mapping. Process and Data modeling with MS VISIO. Worked with Technical Team to create Business Services (Web Services) that Application could leverage using SOA, to create System Architecture and CDM for common order platform. Designed Payment Authorization (Credit Card, Net Terms, and Pay Pal) for the transaction/order entry systems. Implemented A/B Testing, Customer Feedback Functionality to Gateway.com Worked with the DW, ETL teams to create Order entry systems Business Objects reports. (Full Client, Web I) Worked in a cross functional team of Business, Architects and Developers to implement new features. Program Managed Enterprise Order Entry Systems - Development and Deployment Schedule. Developed and maintained User Manuals, Application Documentation Manual, on Share Point tool. Created Test Plansand Test Strategies to define the Objective and Approach of testing. Used Quality Center to track and report system defects and bug fixes. Written modification requests for the bugs in the application and helped developers to track and resolve the problems. Developed and Executed Manual, Automated Functional, GUI, Regression, UAT Test cases using QTP. Gathered, documented and executed Requirements-based, Business process (workflow/user scenario), Data driven test cases for User Acceptance Testing. Created Test Matrix, Used Quality Center for Test Management, track & report system defects and bug fixes. Performed Load, stress Testing's & Analyzed Performance, Response Times. Designed approach, developed visual scripts in order to test client & server side performance under various conditions to identify bottlenecks. Created / developed SQL Queries (TOAD) with several parameters for Backend/DB testing Conducted meetings for project status, issue identification, and parent task review, Progress Reporting. AMC MORTGAGE SERVICES, CA, USA Oct 04 - Dec 05

Business Analyst

The primary objective of this project is to replace the existing Internal Facing Client / Server Applications with a Web enabled Application System, which can be used across all the Business Channels. This project involves wide exposure towards Requirement Analysis, Creating, Executing and Maintaining of Test plans and Test Cases. Demands understanding and testing of Data Warehouse and Data Marts, thorough knowledge of ETL and Reporting, Enhancement of the Legacy System covered all of the business requirements related to Valuations from maintaining the panel of appraisers to ordering, receiving, and reviewing the valuations.

Responsibilities:

Gathered Analyzed, Validated, and Managed and documented the stated Requirements. Interacted with users for verifying requirements, managing change control process, updating existing documentation. Created Functional Requirement Specification documents - that include UML Use case diagrams, scenarios, activity diagrams and data mapping. Provided End User Consulting on Functionality and Business Process. Acted as a client liaison to review priorities and manage the overall client queue. Provided consultation services to clients, technicians and internal departments on basic to intricate functions of the applications. Identified business directions & objectives that may influence the required data and application architectures. Defined, prioritized business requirements, Determine which business subject areas provide the most needed information; prioritize and sequence implementation projects accordingly. Provide relevant test scenarios for the testing team. Work with test team to develop system integration test scripts and ensure the testing results correspond to the business expectations. Used Test Director, QTP, Load Runner for Test management, Functional, GUI, Performance, Stress Testing Perform Data Validation, Data Integration and Backend/DB testing using SQL Queries manually. Created Test input requirements and prepared the test data for data driven testing. Mentored, trained staff about Tech Guide & Company Standards. Set-up and Coordinate Onsite offshore teams, Conduct Knowledge Transfer sessions to the offshore team. Lloyds Bank, UK Aug 03 - Sept 04 Business Analyst Lloyds TSB is leader in Business, Personal and Corporate Banking. Noted financial provider for millions of customers with the financial resources to meet and manage their credit needs and to achieve their financial goals. The Project involves an applicant Information System, Loan Appraisal and Loan Sanction, Legal, Disbursements, Accounts, MIS and Report Modules of a Housing Finance System and Enhancements for their Internet Banking.

Responsibilities:

Translated stakeholder requirements into various documentation deliverables such as functional specifications, use cases, workflow / process diagrams, data flow / data model diagrams. Produced functional specifications and led weekly meetings with developers and business units to discuss outstanding technical issues and deadlines that had to be met. Coordinated project activities between clients and internal groups and information technology, including project portfolio management and project pipeline planning. Provided functional expertise to developers during the technical design and construction phases of the project. Documented and analyzed business workflows and processes. Present the studies to the client for approval Participated in Universe development - planning, designing, Building, distribution, and maintenance phases. Designed and developed Universes by defining Joins, Cardinalities between the tables. Created UML use case, activity diagrams for the interaction between report analyst and the reporting systems. Successfully implemented BPR and achieved improved Performance, Reduced Time and Cost. Developed test plans and scripts; performed client testing for routine to complex processes to ensure proper system functioning. Worked closely with UAT Testers and End Users during system validation, User Acceptance Testing to expose functionality/business logic problems that unit testing and system testing have missed out. Participated in Integration, System, Regression, Performance, and UAT - Using TD, WR, Load Runner Participated in defect review meetings with the team members. Worked closely with the project manager to record, track, prioritize and close bugs. Used CVS to maintain versions between various stages of SDLC. Client: A.G. Edwards, St. Louis, MO May' 2005 - Feb' 2006

Profile: Sr. Business Analyst/System Analyst

Project Profile: A.G. Edwards is a full service Trading based brokerage firm in Internet-based futures, options and forex brokerage. This site allows Users (Financial Representative) to trade online. The main features of this site were: Users can open new account online to trade equitiies, bonds, derivatives and forex with the Trading system using DTCC's applications as a Clearing House agent. The user will get real-time streaming quotes for the currency pairs they selected, their current position in the forex market, summary of work orders, payments and current money balances, P & L Accounts and available trading power, all continuously updating in real time via live quotes. The site also facilitates users to Place, Change and Cancel an Entry Order, Placing a Market Order, Place/Modify/Delete/Close a Stop Loss Limit on an Open Position.

Responsibilities:

Gathered Business requirements pertaining to Trading, equities and Fixed Incomes like bonds, converted the same into functional requirements by implementing the RUP methodology and authored the same in Business Requirement Document (BRD). Designed and developed all Narrative Use Cases and conducted UML modeling like created Use Case Diagrams, Process Flow Diagrams and Activity Diagrams using MS Visio. Implemented the entire Rational Unified Process (RUP) methodology of application development with its various workflows, artifacts and activities. Developed business process models in RUP to document existing and future business processes. Established a business Analysis methodology around the Rational Unified Process. Analyzed user requirements, attended Change Request meetings to document changes and implemented procedures to test changes. Assisted in developing project timelines/deliverables/strategies for effective project management. Evaluated existing practices of storing and handling important financial data for compliance. Involved in developing the test strategy and assisted in developed Test scenarios, test conditions and test cases Partnered with the technical Business Analyst Interview questions  areas in the research, resolution of system and User Acceptance Testing (UAT).

[shivipandey-blog]

In this SQL Interview Questions blog, I will introduce you to the most frequently asked questions on SQL (Structured Query Language). This blog is the perfect guide for you to learn all the concepts related to SQL, Oracle, MS SQL Server and MySQL database.

[siva3155]

300+ TOP SSRS Interview Questions and Answers

SSRS Interview Questions for freshers experienced :-

1. What is Query parameter in SSRS? Query parameters is mentioned in the query of the datasources that are to be included into the SQL script’s WHERE clause of the SQL that can accept parameters. Query parameters begin with the symbol @.The name should not contain spaces and can not begin with numeral. For clarity, we use only letters. 2. What are the Reporting Service Components in SSRS? Report Designer: A place where we can create report. Report Server: Provides services for implementation and delivery of reports. Report Manager: A Web-based administration tool for managing the Report Server. 3. What is a matrix in SSRS? A matrix is a data region linked to a report set. Matrix allows us to create crosstab reports with the report variables displaying on rows and columns. It allows us to drag and drop fields into it. 4. What are sub reports and how to create them? A sub report is like any other reports which can be called in main report and can be generate through main report. Parameters can be passed from main report to sub report and basis of that report can be generated. 5. What is the report model project? Report model project is for creating Adhoc reporting. You can create the adhoc reports through report builder. Report model project can be created on bids or report server. This model can have simple view. And using 6. What is report server project? Report Server Project contains the RDL file and it need to be deployed on report server to view the report files to application and user. It a solution where we design our reports. You can add it by going into BIDS clicking on new item and then selecting reports server project. Once the solution is created you can start creating reports. 7. What is the report builder? Report builder is used to create small reports and it a define interface. You can’t change the report interface in report builder it pre designed. You can just drag columns in the report. Report builder creates reports on database objects available with report model project. 8. In which SQL Server version report builder introduced? Report builder introduced in SQL Server 2005. While creating or deploying report model project on report server you can get error or it might not get created. For this you need to check whether the service pack 22 is installed or not. 9. How to deploy the Report? Report can be deployed in three ways. Using visual studio: In visual studio you can directly deploy the report through solution explorer by providing the report server URL in project properties at Target Server URL. This will deploy entire project or single report as per you selection. Using report server: Can directly go to the report server and deploy the report by browsing the report from the disk location on server. Creating the utility: SQL server provides the utility using that which can be used to create a customize utility for your report deployment in bulk. 10. What is RS.exe utility? Rs.exe utility is used for deploying the report on report server. It comes with the report server and can be customize accordingly.

SSRS Interview Questions 11. What is the name of reporting services config file and what’s it’s used for? Reporting service config file is used for report configuration details. It contains the report format and also the report import types. Report service config reside at ISS. 12. What are the three different part of RDL file explain them? In visual studio RDL files has three parts. Data: It contains the dataset on which we write the query. Data set is connected with data source. Design: In design you can design report. Can create tables and matrix reports. Drag columns values from source. Preview: to check the preview after the report run. 13. Which language rdl files made of? RDL files are written in XML. 14. What is the chart in report? Chart reports are for graphical representation. You can get pie charts columns harts and various other options. 3d charts are also available in reporting services. 15. What is Data Set in report? Data set are the set of data which we want to show in report. Data creates on data source. Data source is the source of data from where we are getting this data i.e. database server and database name connection string. 16. What are the different types of data sources in SSRS? SSRS use different data source. Some of them are listed below. Microsoft SQL Server OLEDB Oracle ODBC SQL Server Analysis Service Report Server Model SAP Net weaver BI Hyperion Teradata XML 17. What is the web service used for reporting services? Reporting Service Web Service used in SSRS. By accessing this web service you can access all report server component and also get the report deployed on report server. 18. How to add the custom code in Report? To add the custom codes in report go to report tab on top then properties and there you will find the options for custom code. 19. What is a cache in SSRS? Report server can lay up a copy of processed report in a memory and return the copy when a user opens the report. This server memory is known as cache and the process is called caching. 20. What is report snapshot in SSRS? Report snapshot is a report which contains layout information and a dataset that is extracted at a particular point of time. When the new snapshot is created the previous report snapshot will be overwritten. 21. What is bookmark link in SSRS? Bookmark is a link which a person clicks to shift to a different area or page in a report. We can insert bookmarks links only to textboxes and images. 22. What is Command parameter in SSRS? A Command parameter is used to forward protocol to the Report Server regarding the item that has been retrieved. Command parameter is also used for rearranging a user’s session information. 23. What is Format parameter in SSRS? Format parameter is used to control report output. Every delivering format on Report Server has to pass through this parameter. 24. What is Snapshot parameter in SSRS? When a report is saved in a snapshot history, it is allocated a time or date to uniquely identify that report. Snapshot parameter is used to get back these historical reports by passing this time or date to get proper report. 25. What are the rendering extensions of SSRS? Rendering extensions manage the category of document produced when a report is processed. Rendering Extensions are: HTML, MHTML, EXCEL, CSV, IMAGE, PDF, and XML. 26. What are the three command line utilities and what are their primary functions? The three command line utilities include RsConfig.exe: It is used to determine the connection properties from the SSRS instance to the Report Server database RsKeyMgmet.exe: It executes scale out deployment set-up and encryption key operations Rs.exe: It executes Report server Script files which can perform management operations and report deployment 27. How you can deploy an SSRS report? SSRS report can be deployed in three ways By Visual Studio: You can directly deploy the report in Visual Studios through solution explorer, by declaring the report server URL in project properties at Target Server URL. By Report Server: By browsing the report from the disk location of the server you can deploy the report to report server By creating the Utility: Another option is to create customized utility to deploy the report 28. What method you can use to reduce the overhead of Reporting Services data sources? Cached reports and Snapshots can be used to reduce the overhead of Reporting Services Sources. 29. What is the difference between Tabular and Matrix report? Tabular Report: Tabular report is the most basic type of report. Each column relates to a column chosen from the database Matrix Report: A matrix report is a cross-tabulation of four groups of data. 30. How would you store your query in an SSRS report or a Database server? Storing SQL queries directly in text format in the data should be avoided. Instead, it should be stored in a stored procedure in the database server. The advantage is that the SQL would be in a compiled format in an SP and gives all the benefits of SP compared to using an ad-hoc query from the report. SSRS Questions and Answers Pdf Download Read the full article

[eswars-tech-world]

Question-100: How do you use the DBMS_ADVISOR package in Oracle to perform SQL tuning and access database advisor recommendations?

Interview Questions on Oracle SQL & PLSQL Development: For more questions like this: Do follow the main blog #oracledatabase #interviewquestions #freshers #beginners #intermediatelevel #experienced #eswarstechworld #oracle #interview #development #sql

The DBMS_ADVISOR package in Oracle is a powerful tool used for SQL tuning and accessing database advisor recommendations. Here’s an explanation of the purpose of the DBMS_ADVISOR package and how it can be used: Definition: The DBMS_ADVISOR package provides a set of procedures and functions to analyze and tune SQL statements and access recommendations from various advisors in Oracle. It helps…

[emmablisa]

Tableau Real Time Interview Questions By experts

If you're looking for Tableau Interview Questions for Experienced or Freshers, you are at right place. There are lot of opportunities from many reputed companies in the world. According to research Tableau has a market share of about 16.2%. So, You still have opportunity to move ahead in your career in Tableau Analytics. Mindmajix offers Advanced Tableau Interview Questions 2018 that helps you in cracking your interview & acquire dream career as Tableau Developer.

 Q. What is the difference between context filter to other filters?

Whenever we crate context filter

·         Tableau will create a temporary table for this particular filter set and other filters will be apply on context filter data like cascade parameters… suppose we have crated context filter on countries >> we have chosen country as USA and India

·         Tableau will create a temporary table for this two countries data and if you have any other filers >>other will be apply on this two countries data if we don’t have any context filter

·         Each and individual record will check for all filters

Q. What is disadvantage of context filters?

·         The context filter is not frequently changed by the user – if the filter is changed the database must recomputed and rewrite the temporary table, slowing performance.

·         When you set a dimension to context, Tableau crates a temporary table that will require a reload each time the view is initiated. For Excel, Access and text data sources, the temporary table created is in an Access table format. For SQL Server, My SQL and Oracle data sources, you must have permission to create a temporary table on your server. For multidimensional data source, or cubes, temporary tables are not crated, and context filters only defined which filters are independent and dependent.

Q. What are the five main product offered by Tableau company? Tableau offers five main products: Tableau Desktop, Tableau Server, Tableau Online, Tableau reader and Tableau Public.

Q. What is the current latest version of Tableau Desktop(as of Sep, 25th 2017)?

Current version: Tableau Desktop Version 10.4

Q. What is data visualization? Data visualization refers to the techniques used to communicate data or information by encoding it as visual objects (e.g. points, lines or bars) contained in graphics.

Interested in mastering Tableau Course? Enroll now for FREE demo on Tableau Training.

Q. Why tableau? Whether your data is in an on-premise database, a database, a data warehouse, a cloud application or an Excel file, you can analyze it with Tableau. You can create views of your data and share it with colleagues, customers, and partners. You can use Tableau to blend it with other data. And you can keep your data up to date automatically.

[askinternationalfan]

REAL TIME TRAINING

PROFESSIONAL REAL TIME TRAINING:

Training Includes:

ORACLE DBA (LIVE + RECORDING SESSIONS)

ORACLE DBA WEB APP(LIVE +RECORDING SESSIONS)

POSTGRE SQL(LIVE + RECORDING SESSIONS)

MY SQL (LIVE + RECORDING SESSIONS)

SQL

PLSQL (LIVE + RECORDING SESSIONS)

GOLDENGATE (LIVE + RECORDING SESSIONS)

REAL APPLICAION CLUSTER (LIVE + RECORDING SESSIONS)

PEOPLE SOFT

C# ( LIVE )

ASP.NET(LIVE)

.NET FRAME WORK LATEST VERSIONS(2017,2018)

MANUAL TESTING ( BY REAL TIME TESTER)

Note : Not from any institution. Direct training from Real Time professional.

Advance features

Real Time project

Interview questions

Standard Material

Daily Practice File

Daily Record Video

Mock Interviews

Details:

------------

Documents:

-------------------

Recorded videos : yes

Practice file : yes

Material yes: yes

Interview questions: yes

Prerequisites to learn

Not required anything. You can start fresh technology.

Non-IT people:

No worries. You can learn very easily. Don't think too much and just start without second thought.

Training Eligible for :

Freshers and Experience people upto 6 years.

Ping me in watsapp for any clarifications.

+91-9540662806

@@@ BEST OF LUCK YOUR FUTUER @@@

[krispytacomagazine]

The Chronicles of Sql Technical Interview Questions

How to Locate Sql Technical Interview Questions Online Make certain you understand every question. If you set in the interview room backed by a couple of years of DBMS Administration experience you are going to be asked questions on your own real life encounter. http://bit.ly/2jYDMm8 Questions change greatly due to position you're recruiting for. Below you'll find some questions that are general to find more information regarding the SQL abilities of candidate. It is a fact that the above questions could possibly be answered in many of potential ways. https://is.gd/kqrdm4 Almost invariably, if you're a fresher you're going to be asked issues, or you are a Database Administrator. You must answer some questions concerning SSRS, to start in the business. Continue reading and you will discover an answer. The solution ought to be one. The issue is you will need to express the answer in your words and imagination will come out in front of the interviewers. What Does Sql Technical Interview Questions Mean? When we encounter an issue the first thing we must do would be to realize the issue. The matter is the SQL Standard claims that we cannot opt unless it included within an function for a column which is not part of the group by clause. The problem here is that we don't understand because we're not certain with what we're asking for in the SQL, what will be returned! The moment you've understood what the issue is, the thing would be to write down the problem in words and the potential sources of input. Brush, if your expertise is a bit light. My experience is that I get a great deal out of Oracle classes if I have finished a legwork that is tiny to start with to what is being taught to find some vulnerability. Immediate experience is always the best thing, although knowledge gained through conference sessions is a source of knowledge that is high-quality. An understanding of SQL commands is outstanding, but exactly what the company will need from you is to have the ability to utilize that knowledge to mine the data pertinent to a analysis from the database. You want the database skills and then the meeting skills are required by you. The vital skills for the particular database might be obtained on the job. https://annuaire-du-net.com/sql-interview-questions-and-answers/ You could potentially be asked to describe the reasons you left the previous job. Jobs may be executed on demand. Therefore, if you're looking you have to prepare for the 2019 SSIS interview questions. PSRELEASE You may have observed there are many folks, who be able to find the jobs easily and you end up at the receiving end. Interviews are a superb method to assess abilities that are soft . If you are choosing an in person interview then make certain you dress up. Interviews have a couple of questions. An interview enables the candidate to gauge demands of this job and the culture. For all those professionals that are looking forward to attending Oracle Database Administrator interview in late times, following are a few. Your interviewer is trying to observe the way that your personality will donate to some work culture that is positive, open. The interviewer would like to know you find it feasible when items aren't uncomplicated to be flexible and resourceful. He or she wishes to know what you are looking for that you didn't find in your occupation. He or she wants to know you can resolve problems yourself, and which you are capable of handling situations. Your interviewer will likely need to look at your knowledge to see if you may name the most crucial orders necessary for programmers. Nearly all the time interviewer wants to see how you believe. Coding tests may be used in two manners. As a way to display accurate, they need to be the ones that are ideal. Programming evaluations that are online are occasionally a procedure. There are some SQL tests on the market but often they confirm candidate understanding instead of skills and programming abilities to deal with real life difficulties. https://tinyurl.com/y2emjnh4 Matters You Won't Like About Sql Technical Interview Questions and Things You Will Information can be saved only in 1 sequence on disk. Data control language (DCL) is a kind of syntax that is comparable to a computer programming language. There are assorted kinds of connect which could possibly be used to recover data and it's dependent on the association between tables.

[differentpersonafire-blog]

How to Find Sql Technical Interview Questions Online Make certain you understand every question. You're going to be asked questions in your own real-life experience, if you set in the interview area. Questions vary greatly due to specific skills you want to verify for and position you are recruiting. Below you will find some questions to find details regarding candidate's SQL abilities. It is a fact that the above questions could be answered in many of other ways. Almost invariably, you are going to be asked issues, regardless if you're a fresher, or you are a Database Administrator. You must answer a few questions regarding SSRS, to start in the company. Continue on reading and you will find an answer. The answer ought to be one. The key issue is that need to express the answer in your words and creativity will come out at front of their interviewers. When we encounter an issue, the very first thing we must do would be to realize the issue. The matter is that the SQL Standard claims that we can't opt unless it is also included inside an function, for a column that isn't a part of this group by clause. The issue here is that we do not know what's going to be returned because we're not with what we're asking for in the SQL specific! As soon as you have understood what the matter is, the next thing would be to write down the issue in words and also the potential sources of input to the model. If your experience is a small bit light, brush. My experience is that I receive a fantastic deal more out of Oracle courses if I've finished a legwork to start with to what is being taught to come across some exposure. Knowledge is also a source of expertise that is high quality, but direct experience is always the best thing. What the corporation will need from you would be to be able to utilize that knowledge to mine the data pertinent to a existing analysis from the database, although an encyclopedic understanding of SQL commands is outstanding. https://tinyurl.com/y2emjnh4 You want the database skills and then the interview abilities are required by you. http://bit.ly/2jYDMm8 The skills for the specific database may be obtained at work. You might possibly be asked to describe the reasons you left the occupation. Jobs may be implemented on demand. So If you're looking for work that's connected to SSIS, you must prepare yourself for the 2019 SSIS interview questions. PSRELEASE you might have observed you will find lots of lesser skilled folks, who be able to find the jobs and you end up in the receiving end. Interviews are a superb way to assess skills . If you're choosing an in person interview afterward ensure you dress up properly. Most interviews contain a couple of behavioral questions. An interview also enables the candidate to estimate the corporate culture and demands of the job. For all those professionals which are excited about attending Oracle Database Administrator interview in late times, following are some of the most popular interview questions and answers which will definitely assist you in the appropriate way. Your interviewer is trying to observe the way that your character will contribute to some work culture that is open. The interviewer would love to know you discover that it's possible when items are not straightforward to be flexible and resourceful. He or she wants to understand what you are currently looking for that you did not find on your previous occupation. She or he wants to know which you're capable of handling situations, and you can solve problems yourself without external intervention. Your interviewer will probably need to look at your knowledge to find out if you will name the most crucial commands necessary for programmers. https://annuaire-du-net.com/sql-interview-questions-and-answers/ The majority of the time interviewer wants to see how you think. Coding tests may be used in two manners. In order to screen accurate, they have to be the ones that are right. Coding tests that are online are a terrific procedure to screen SQL abilities. There are some SQL tests available on the marketplace but often they verify candidate's academic knowledge instead of programming abilities and skills to address real life difficulties. Things You Will Not Like About Sql Technical Interview Questions and Matters You Will Data can be saved only in 1 order on disc. https://is.gd/kqrdm4 Data control language (DCL) is a sort of syntax that is comparable to a computer programming language. There are and it's dependent on the association between tables.

[swimmingkoalakitty-blog]

The Chronicles of Sql Technical Interview Questions

How to Find Sql Technical Interview Questions Online Make certain you understand every question. http://bit.ly/2jYDMm8 If you put in the interview room you are going to be asked questions in your own real-life experience. https://is.gd/kqrdm4 Questions change due to particular skills you need to confirm for and position you are recruiting. Below you'll find some general questions to find more details about candidate's SQL skills. It is a simple fact that the aforementioned questions may be answered in many of other ways that are possible. Almost invariably, if you're a fresher you are likely to be asked questions, no matter, or you're a Database Administrator. You must answer some technical questions regarding SSRS to start in the business. https://tinyurl.com/y2emjnh4 Just continue on reading and you'll find an answer. The solution should be one. The key issue is you will need to express the solution on your words and imagination will come out in front of their interviewers. What Does Sql Technical Interview Questions Mean? The very first thing we must do if we encounter an issue is to realize the issue. https://annuaire-du-net.com/sql-interview-questions-and-answers/ The issue is that the SQL Standard claims that we cannot elect unless it contained inside an function, for a column which is not part of this group by clause. The problem here is that we don't understand what's going to be returned because we are not with what we are requesting in the SQL specific enough! As soon as you've understood what the matter is, the thing to do is to write down the problem in words and the potential sources of input to the model. If your experience is a little bit mild, brush up as much as you're in a position to. My experience is that I get a fantastic deal out of Oracle classes if I have completed a legwork to start with to what is being taught to find some exposure. Experience is the best thing, although knowledge gained through seminar sessions is a superior source of knowledge that is high-quality. What the company will need from you would be to be able to use that knowledge to mine the information pertinent to a present-day analysis from the database, although an understanding of SQL commands is outstanding. First you want the database abilities and then the meeting abilities are required by you. The essential skills for the specific database may be obtained at work. You could possibly be asked to spell out the reasons you left your job. Jobs may be executed on demand. Therefore, if you're looking you have to prepare yourself for the 2019 SSIS interview questions. PSRELEASE You may have observed you will find lots of people, who be able to find the tasks easily and you end up at the receiving end. Interviews are a superb way to assess skills , too. You dress up correctly if you are choosing an in person interview afterward ensure. Most interviews have a couple of behavioral questions. An interview enables the candidate to estimate needs of the job and the culture. For all those professionals that are excited about attending Oracle Database Administrator interview in times, following are some. Your interviewer is attempting to observe the way that your personality will donate to some work culture. The interviewer would love to know you find it feasible to be resourceful and flexible when items are not uncomplicated. He or she wants to understand what you're searching for which you did not find on your occupation. He or she wishes to know that you can solve problems yourself without external intervention, and that you are capable of handling situations. Your interviewer will likely want to look at your knowledge to find out if you will name the indispensable commands. Coding tests might be used in two ways. In order to screen accurate, they have to be the right ones. Coding tests are sometimes a great method. There are some SQL online tests on the marketplace but often they confirm the academic understanding instead of skills and programming skills of candidate to deal with real life difficulties. Things You Will Not Like Things and About Sql Technical Interview Questions You Will Data can be stored in 1 order on disc. Data control language (DCL) is a kind of syntax that's comparable to a computer programming language. There are and it is dependent on the association between tables.

[holeback8-blog]

The Chronicles of Sql Technical Interview Questions

How to Find Sql Technical Interview Questions Online Make certain you understand each question. You are going to be asked questions on your own real-life encounter, if you put in the interview area backed by a few years of DBMS Administration expertise. Evidently, questions vary greatly due to particular skills you need to confirm for and position you are recruiting. Below you will find some questions that are general to find information regarding candidate's SQL abilities. It is a fact that the questions could be answered in a number of ways that are possible. Almost invariably, if you're a fresher, you're likely to be asked issues, regardless, or you're a Database Administrator. You must answer a few questions concerning SSRS, to begin in the company. Just continue reading and you'll find an answer. https://tinyurl.com/y2emjnh4 The solution should be one. The key issue is you need to express the solution in your words and imagination will come out at front of their interviewers. What Exactly Does Sql Technical Interview Questions Mean? When we encounter an issue the very first thing we must do would be to realize the issue. here are the findings The issue is that the SQL Standard asserts that we cannot elect for a column which is not part of the group by clause unless it's also included within an aggregate function. The problem here is that we do not know because we are not specific enough with what we're asking for in the SQL, what will be returned! The moment you have known what the issue is, the next thing would be to write down the issue in words and also the sources of input. Brush if your experience is a bit light. My experience is that I receive a fantastic deal out of Oracle courses if I have finished a legwork that is very small to begin with to what is being taught, to come across some exposure. Direct experience is the best thing, although knowledge gained through conference sessions is a superior source of expertise. An encyclopedic comprehension of SQL commands is excellent, but exactly what the corporation will need from you is to have the ability to use that knowledge to mine the information pertinent to your existing analysis from the database. You want the database skills and then the interview abilities are required by you. The skills for the specific database might be acquired at work. You could possibly be asked to spell out the reasons you left the previous job. sql interview questions top Jobs may be executed on demand. Therefore, if you're looking you must prepare for your 2019 SSIS interview queries. PSRELEASE you might have observed there are many people, who manage to locate the tasks easily and you wind up in the receiving end. Interviews are an excellent method to assess abilities . You dress up 16, if you're choosing an in person interview make certain. Interviews have a couple of questions that are behavioral. https://annuaire-du-net.com/sql-interview-questions-and-answers/ An interview also enables the candidate to gauge demands of the job and the corporate culture. For those professionals that are excited about attending Oracle Database Administrator interview in late times, below are a few of the interview questions and answers that will help you in the proper way. Your interviewer is attempting to observe the way your character will contribute to some work culture. The interviewer would love to know you find it possible when things are not straightforward to become flexible and resourceful. She or he wants to understand what you're looking for which you didn't find in your occupation. She or he wishes to know you can resolve problems yourself without external intervention, and that you're capable of handling situations. Your interviewer will likely need to check out your knowledge to see whether you may name the most crucial commands required for programmers. Coding tests might be utilised in two manners. As a way to screen accurate, they need to be the ones that are ideal. Programming evaluations that are online are a procedure to screen SQL abilities. There are some SQL tests on the market but frequently they confirm the academic knowledge instead of skills and programming skills of candidate to deal with real life problems. Matters You Will Not Like Things and About Sql Technical Interview Questions You Will Data can be stored in 1 sequence on disk. Data control language (DCL) is a sort of syntax that is comparable to a computer programming language. There are types of connect which could possibly be used to recover data and it's dependent on the association between tables.

[corkangora2-blog]

How to Find Sql Technical Interview Questions Online Make certain you understand each question. https://tinyurl.com/y2emjnh4 You're likely to be asked questions on your own real life encounter that is , if you set in the interview area backed by a couple of years of DBMS Administration expertise. Questions vary greatly because of position you're recruiting for. Below you'll find some questions to find more information about candidate's SQL skills. It is a fact that the questions may be answered in a number of other ways that are possible. Almost invariably, you are going to be asked questions if you're a fresher, or you are a Database Administrator. https://is.gd/kqrdm4 To start in the company, you must answer some technical questions concerning SSRS. Continue reading and you will discover an answer. The solution ought to be one. The issue is that will need to express the answer in your words in order for your skills and imagination will come out at the front of the interviewers. The first thing we have to do when we encounter a problem would be to realize the issue. The issue is that the SQL Standard asserts that we cannot opt unless it included within an aggregate function for a column which is not a part of the group by clause. http://bit.ly/2jYDMm8 The issue here is that we don't understand what will be returned because we're not specific enough with what we are requesting in the SQL! As soon as you've known what the issue is, the thing to do is to write down the problem in words and the prospective sources of input to the model. Brush as much as you are in a position to, if your experience is a bit light. My experience is that I get a great deal more out of Oracle classes if I have finished a legwork to start with to what is being taught to find some exposure. Knowledge is a superior supply of knowledge that is high quality, but experience is always the best thing. Exactly what the corporation will need from you is to be able to utilize that knowledge to mine the information pertinent to a analysis from the database, although an encyclopedic comprehension of SQL commands is outstanding. First you want the database abilities and then the interview skills are required by you. The skills for the specific database might nevertheless be acquired on the job. You might be asked to describe the reasons you left the occupation. Jobs may be implemented on demand. So If you're looking you must prepare yourself for your 2019 SSIS interview questions. PSRELEASE you might have observed there are lesser skilled folks, who manage to find the jobs and you end up at the receiving end. Interviews are an excellent method to assess soft abilities, also. You dress up 16, if you're going for an in person interview afterward ensure. Most interviews have a few behavioral questions. An interview also enables the candidate to estimate the corporate culture and demands of this job. For all those professionals which are excited about attending Oracle Database Administrator interview in times, below are a few of the interview questions and answers which will definitely assist you. Your interviewer is currently trying to observe the way your personality will contribute to some work culture. The interviewer would love to know you discover that it's possible to be resourceful and flexible when things aren't uncomplicated. He or she wants to know what you are looking for which you didn't find on your prior job. She or he wants to know that you are capable of handling situations, and that you can resolve problems yourself. Your interviewer will likely need to look at your knowledge to find out if you will name the crucial commands. Coding evaluations might be utilised in two manners. In order to display accurate, they have to be the ones that are ideal. https://annuaire-du-net.com/sql-interview-questions-and-answers/ Coding tests that are online are occasionally a great method. There are some SQL tests available on the market but frequently they confirm candidate's academic knowledge instead of abilities and programming abilities to deal with real life problems. Matters You Won't Like Things and About Sql Technical Interview Questions You Will Data can be saved only in 1 order on disk. Data control language (DCL) is a sort of syntax that is comparable to some computer programming language. There are various types of connect that may be utilized to retrieve data and it is determined by the association between tables.

[siva3155]

300+ TOP ORACLE Database Interview Questions and Answers

ORACLE Database Interview Questions for freshers experienced :-

1. What Is Oracle? Oracle is a company. Oracle is also a database server, which manages data in a very structured way. It allows users to store and retrieve related data in a multiuser environment so that many users can concurrently access the same data. All this is accomplished while delivering high performance. A database server also prevents unauthorized access and provides efficient solutions for failure recovery. 2. What Is an Oracle Database? An Oracle database is a collection of data treated as a big unit in the database server. 3. What Is an Oracle Instance? Every running Oracle database is associated with an Oracle instance. When a database is started on a database server (regardless of the type of computer), Oracle allocates a memory area called the System Global Area (SGA) and starts one or more Oracle processes. This combination of the SGA and the Oracle processes is called an Oracle instance. The memory and processes of an instance manage the associated database's data efficiently and serve the one or multiple users of the database. 4. What Is a Parameter File in Oracle? A parameter file is a file that contains a list of initialization parameters and a value for each parameter. You specify initialization parameters in a parameter file that reflect your particular installation. Oracle supports the following two types of parameter files: * Server Parameter Files - Binary version. Persistent. * Initialization Parameter Files - Text version. Not persistent. 5. What Is a Server Parameter File in Oracle? A server parameter file is a binary file that acts as a repository for initialization parameters. The server parameter file can reside on the machine where the Oracle database server executes. Initialization parameters stored in a server parameter file are persistent, in that any changes made to the parameters while an instance is running can persist across instance shutdown and startup. 6. What Is a Initialization Parameter File in Oracle? An initialization parameter file is a text file that contains a list of initialization parameters. The file should be written in the client's default character set. Sample initialization parameter files are provided on the Oracle distribution medium for each operating system. A sample file is sufficient for initial use, but you will probably want to modify the file to tune the database for best performance. Any changes will take effect after you completely shut down and restart the instance. 7. What is System Global Area (SGA) in Oracle? The System Global Area (SGA) is a memory area that contains data shared between all database users such as buffer cache and a shared pool of SQL statements. The SGA is allocated in memory when an Oracle database instance is started, and any change in the value will take effect at the next startup. 8. What is Program Global Area (PGA) in Oracle? A Program Global Area (PGA) is a memory buffer that is allocated for each individual database session and it contains session specific information such as SQL statement data or buffers used for sorting. The value specifies the total memory allocated by all sessions, and changes will take effect as new sessions are started. 9. What Is a User Account in Oracle? A user account is identified by a user name and defines the user's attributes, including the following: Password for database authentication Privileges and roles Default tablespace for database objects Default temporary tablespace for query processing work space 10. What Is the Relation of a User Account and a Schema in Oracle? User accounts and schemas have a one-to-one relation. When you create a user, you are also implicitly creating a schema for that user. A schema is a logical container for the database objects (such as tables, views, triggers, and so on) that the user creates. The schema name is the same as the user name, and can be used to unambiguously refer to objects owned by the user.

ORACLE Database Interview Questions 11. What Is a User Role in Oracle? A user role is a group of privileges. Privileges are assigned to users through user roles. You create new roles, grant privileges to the roles, and then grant roles to users. 12. What is a Database Schema in Oracle? A schema is a collection of logical structures of data, or schema objects. A schema is owned by a database user and has the same name as that user. Each user owns a single schema. Schema objects can be created and manipulated with SQL and include: tables, views, and other types of data objects. 13. What Is a Database Table in Oracle? A database table is a basic unit of data logical storage in an Oracle database. Data is stored in rows and columns. You define a table with a table name, such as employees, and a set of columns. You give each column a column name, such as employee_id, last_name, and job_id; a datatype, such as VARCHAR2, DATE, or NUMBER; and a width. The width can be predetermined by the datatype, as in DATE. If columns are of the NUMBER datatype, define precision and scale instead of width. A row is a collection of column information corresponding to a single record. 14. What Is a Table Index in Oracle? Index is an optional structure associated with a table that allow SQL statements to execute more quickly against a table. Just as the index in this manual helps you locate information faster than if there were no index, an Oracle Database index provides a faster access path to table data. You can use indexes without rewriting any queries. Your results are the same, but you see them more quickly. 15. What Is an Oracle Tablespace? An Oracle tablespace is a big unit of logical storage in an Oracle database. It is managed and used by the Oracle server to store structures data objects, like tables and indexes. 16. What Is an Oracle Data File? An Oracle data file is a big unit of physical storage in the OS file system. One or many Oracle data files are organized together to provide physical storage to a single Oracle tablespace. 17. What Is a Static Data Dictionary in Oracle? Data dictionary tables are not directly accessible, but you can access information in them through data dictionary views. To list the data dictionary views available to you, query the view DICTIONARY. Many data dictionary tables have three corresponding views: * An ALL_ view displays all the information accessible to the current user, including information from the current user's schema as well as information from objects in other schemas, if the current user has access to those objects by way of grants of privileges or roles. * A DBA_ view displays all relevant information in the entire database. DBA_ views are intended only for administrators. They can be accessed only by users with the SELECT ANY TABLE privilege. This privilege is assigned to the DBA role when the system is initially installed. * A USER_ view displays all the information from the schema of the current user. No special privileges are required to query these views. 18. What Is a Dynamic Performance View in Oracle? Oracle contains a set of underlying views that are maintained by the database server and accessible to the database administrator user SYS. These views are called dynamic performance views because they are continuously updated while a database is open and in use, and their contents relate primarily to performance. Although these views appear to be regular database tables, they are not. These views provide data on internal disk structures and memory structures. You can select from these views, but you can never update or alter them. 19. What Is a Recycle Bin in Oracle? Recycle bin is a logical storage to hold the tables that have been dropped from the database, in case it was dropped in error. Tables in recycle bin can be recovered back into database by the Flashback Drop action. Oracle database recycle save the same purpose as the recycle bin on your Windows desktop. Recycle bin can be turned on or off in the recyclebin=on/off in your parameter file. 20. What Is SQL*Plus? SQL*Plus is an interactive and batch query tool that is installed with every Oracle Database Server or Client installation. It has a command-line user interface, a Windows Graphical User Interface (GUI) and the iSQL*Plus web-based user interface. 21. What Is Transport Network Substrate (TNS) in Oracle? TNS, Transport Network Substrate, is a foundation technology, built into the Oracle Net foundation layer that works with any standard network transport protocol. 22. What Is Open Database Communication (ODBC) in Oracle? ODBC, Open Database Communication, a standard API (application program interface) developed by Microsoft for Windows applications to communicate with database management systems. Oracle offers ODBC drivers to allow Windows applications to connect Oracle server through ODBC. 23. What is Oracle Database 10g Express Edition? Based on Oracle Web site: Oracle Database 10g Express Edition (Oracle Database XE) is an entry-level, small-footprint database based on the Oracle Database 10g Release 2 code base that's free to develop, deploy, and distribute; fast to download; and simple to administer. Oracle Database XE is a great starter database for: Developers working on PHP, Java, .NET, and Open Source applications DBAs who need a free, starter database for training and deployment Independent Software Vendors (ISVs) and hardware vendors who want a starter database to distribute free of charge Educational institutions and students who need a free database for their curriculum 24. What Are the Limitations Oracle Database 10g XE? Oracle Database XE is free for runtime usage with the following limitations: Supports up to 4GB of user data (in addition to Oracle system data) Single instance only of Oracle Database XE on any server May be installed on a multiple CPU server, but only executes on one processor in any server May be installed on a server with any amount of memory, but will only use up to 1GB RAM of available memory 25. What Operating Systems Are Supported by Oracle Database 10g XE? Oracle Database 10g Express Edition is available for two types of operating Systems: Linux x86 - Debian, Mandriva, Novell, Red Hat and Ubuntu Microsoft Windows 26. How To Download Oracle Database 10g XE? If you want to download a copy of Oracle Database 10g Express Edition, visit http://www.oracle.com/technology/software/products/database/xe/. If you are using Windows systems, there are downloads available for you: Oracle Database 10g Express Edition (Western European) - Single-byte LATIN1 database for Western European language storage, with the Database Homepage user interface in English only. Oracle Database 10g Express Edition (Universal) - Multi-byte Unicode database for all language deployment, with the Database Homepage user interface available in the following languages: Brazilian Portuguese, Chinese (Simplified and Traditional), English, French, German, Italian, Japanese, Korean and Spanish. Oracle Database 10g Express Client You need to download the universal edition, OracleXEUniv.exe, (216,933,372 bytes) and client package, OracleXEClient.exe (30,943,220 bytes). 27. How To Install Oracle Database 10g XE? To install 10g universal edition, double click, OracleXEUniv.exe, the install wizard starts. It will guide you to finish the installation process. You should take notes about: The SYSTEM password you selecte: atoztarget. Database server port: 1521. Database HTTP port: 8080. MS Transaction Server port: 2030. The directory where 10g XE is installed: oraclexe Hard disk space taken: 1655MB. 28. How To Check Your Oracle Database 10g XE Installation? If you want to check your fresh installation of 10g Express Edition without using any special client programs, you can use a Web browser with this address, http://localhost:8080/apex/. You will see the login page. Enter SYSTEM as the user name, and the password (atoztarget), you selected during the installation to log into the server. Visit different areas on your 10g XE server home page to make sure your server is running OK. You can also get to your 10g XE server home page by going through the start menu. Select All Programs, then Oracle Database 10g Express Edition, and then Go To Database Home Page 29. How To Shutdown Your 10g XE Server? If you want to shutdown your 10g Express Edition server, go to the Services manager in the control panel. You will a service called OracleServiceXE, which represents your 10g Express Edition server. Select OracleServiceXE, and use the right mouse click to stop this service. This will shutdown your 10g Express Edition server. You can also shutdown your 10g XE server through the start menu. Select All Programs, then Oracle Database 10g Express Edition, and then Stop Database. 30. How To Start Your 10g XE Server? Go to the Start menu, select All Programs, Oracle Database 10g Express Edition, and Start Database. 31. How Much Memory Your 10g XE Server Is Using? Your 10g XE Server is using about 180MB of memory even there is no users on the server. The server memory usage is displayed on your server home page, if you log in as SYSTEM. 32. How To Start Your 10g XE Server from Command Line? You can start your 10g XE server from command line by: Open a command line window. Change directory to oraclexeapporacleproduct10.2.0serverBIN. Run StartDB.bat. The batch file StartDB.bat contains: net start OracleXETNSListener net start OracleServiceXE @oradim -startup -sid XE -starttype inst > nul 2>&1 33. How To Shutdown Your 10g XE Server from Command Line? You can shutdown your 10g XE server from command line by: Open a command line window. Change directory to oraclexeapporacleproduct10.2.0serverBIN. Run StopDB.bat. The batch file StopDB.bat contains: net stop OracleServiceXE 34. How To Unlock the Sample User Account in Oracle? Your 10g XE server comes with a sample database user account called HR. But this account is locked. You must unlock it before you can use it: Log into the server home page as SYSTEM. Click the Administration icon, and then click Database Users. Click the HR schema icon to display the user information for HR. Enter a new password (hr) for HR, and change the status to Unlocked. Click Alter User to save the changes. Now user account HR is ready to use. 35. How To Change System Global Area (SGA) in Oracle? Your 10g XE server has a default setting for System Global Area (SGA) of 140MB. The SGA size can be changed to a new value depending on how many concurrent sessions connecting to your server. If you are running this server just for yourself to improve your DBA skill, you should change the SGA size to 32MB by: Log into the server home page as SYSTEM. Go to Administration, then Memory. Click Configure SGA. Enter the new memory size: 32 Click Apply Changes to save the changes. Re-start your server. 36. How To Change Program Global Area (PGA) in Oracle? Your 10g XE server has a default setting for Program Global Area (PGA) of 40MB. The PGA size can be changed to a new value depending on how much data a single session should be allocated. If you think your session will be short with a small amount of data, you should change the PGA size to 16MB by: Log into the server home page as SYSTEM. Go to Administration, then Memory. Click Configure PGA. Enter the new memory size: 16 Click Apply Changes to save the changes. Re-start your server. 37. What Happens If You Set the SGA Too Low in Oracle? Let's you made a mistake and changed to SGA to 16MB from the SYSTEM admin home page. When you run the batch file StartDB.bat, it will return a message saying server stated. However, if you try to connect to your server home page: http://localhost:8080/apex/, you will get no response. Why? Your server is running, but the default instance XE was not started. If you go the Control Panel and Services, you will see service OracleServiceXE is listed not in the running status. 38. What To Do If the StartBD.bat Failed to Start the XE Instance? If StartBD.bat failed to start the XE instance, you need to try to start the instance with other approaches to get detail error messages on why the instance can not be started. One good approach to start the default instance is to use SQL*Plus. Here is how to use SQL*Plus to start the default instance in a command window: >cd (OracleXE home directory) >.binstartdb >.binsqlplus Enter user-name: SYSTEM Enter password: atoztarget ERROR: ORA-01034: ORACLE not available ORA-27101: shared memory realm does not exist The first "cd" is to move the current directory the 10g XE home directory. The second command ".binstartdb" is to make sure the TNS listener is running. The third command ".binsqlplus" launches SQL*Plus. The error message "ORA-27101" tells you that there is a memory problem with the default instance. So you can not use the normal login process to the server without a good instance. See other tips on how to log into a server without any instance. 39. How To Login to the Server without an Instance? If your default instance is in trouble, and you can not use the normal login process to reach the server, you can use a special login to log into the server without any instance. Here is how to use SQL*Plus to log in as as a system BDA: >cd (OracleXE home directory) >.binstartdb >.binsqlplus Enter user-name: SYSTEM/atoztarget AS SYSDBA Connected to an idle instance SQL> show instance instance "local" The trick is to put user name, password and login options in a single string as the user name. "AS SYSDBA" tells the server to not start any instance, and connect the session the idle instance. Log in as SYSDBA is very useful for performing DBA tasks. 40. How To Use "startup" Command to Start Default Instance? If you logged in to the server as a SYSDBA, you start the default instance with the "startup" command. Here is how to start the default instance in SQL*Plus in SYSDBA mode: >.binsqlplus Enter user-name: SYSTEM/atoztarget AS SYSDBA Connected to an idle instance SQL> show instance instance "local" SQL> startup ORA-00821: Specified value of sga_target 16M is too small, needs to be at least 20M Now the server is telling you more details about the memory problem on your default instance: your SGA setting of 16MB is too small. It must be increased to at least 20MB. 41. Where Are the Settings Stored for Each Instance in Oracle? Settings for each instance are stored in a file called Server Parameter File (SPFile). Oracle supports two types of parameter files, Text type, and Binary type. parameter files should be located in $ORACLE_HOMEdatabase directory. A parameter file should be named like "init$SID.ora", where $SID is the instance name. 42. What To Do If the Binary SPFile Is Wrong for the Default Instance? Let's say the SPFile for the default instance is a binary file, and some settings are wrong in the SPFile, like SGA setting is bellow 20MB, how do you change a setting in the binary file? This seems to be a hard task, because the binary SPFile is not allowed to be edited manually. It needs to be updated by the server with instance started. But you can not start the instance because the SPFile has a wrong setting. One way to solve the problem is to stop using the binary SPFile, and use a text version of the a parameter file to start the instance. Here is an example of how to use the backup copy (text version) of the parameter file for the default instance to start the instance: >.binsqlplus Enter user-name: SYSTEM/atoztarget AS SYSDBA Connected to an idle instance 43. How To Check the Server Version in Oracle? Oracle server version information is stored in a table called: PRODUCT_COMPONENT_VERSION. You can use a simple SELECT statement to view the version information like this: >.binsqlplus Enter user-name: SYSTEM/atoztarget AS SYSDBA Connected to an idle instance SQL> COL PRODUCT FORMAT A35 SQL> COL VERSION FORMAT A15 SQL> COL STATUS FORMAT A15 SQL> SELECT * FROM PRODUCT_COMPONENT_VERSION; PRODUCT VERSION STATUS ----------------------------------- ----------- ---------- NLSRTL 10.2.0.1.0 Production Oracle Database 10g Express Edition 10.2.0.1.0 Product PL/SQL 10.2.0.1.0 Production TNS for 32-bit Windows: 10.2.0.1.0 Production 44. Explain What Is SQL*Plus? SQL*Plus is an interactive and batch query tool that is installed with every Oracle Database Server or Client installation. It has a command-line user interface, a Windows Graphical User Interface (GUI) and the iSQL*Plus web-based user interface. SQL*Plus has its own commands and environment, and it provides access to the Oracle Database. It enables you to enter and execute SQL, PL/SQL, SQL*Plus and operating system commands to perform the following: Format, perform calculations on, store, and print from query results Examine table and object definitions Develop and run batch scripts Perform database administration You can use SQL*Plus to generate reports interactively, to generate reports as batch processes, and to output the results to text file, to screen, or to HTML file for browsing on the Internet. You can generate reports dynamically using the HTML output facility of SQL*Plus, or using the dynamic reporting capability of iSQL*Plus to run a script from a web page. 45. How To Start the Command-Line SQL*Plus? f you Oracle server or client installed on your windows system, you can start the command-line SQL*Plus in two ways: 1. Click Start > All Programs > Oracle ... > Start SQL Command Line. The SQL*Plus command window will show up with a message like this: SQL*Plus: Release 10.2.0.1.0 - Production on Tue ... Copyright (c) 1982, 2005, Oracle. All rights reserved. SQL> 2. Click Start > Run..., enter "cmd" and click OK. A Windows command window will show up. You can then use Windows commands to start the command-line SQL*Plus as shown in the tutorial exercise below: >cd c:oraclexeapporacleproduct10.2.0server >.binsqlplus /nolog SQL*Plus: Release 10.2.0.1.0 - Production on Tue ... Copyright (c) 1982, 2005, Oracle. All rights reserved. 46. How To Get Help at the SQL Prompt? Once SQL*Plus is started, you will get a SQL prompt like this: SQL>. This where you can enter commands for SQL*Plus to run. To get help information at the SQL prompt, you can use the HELP command as shown in the following tutorial example: SQL> HELP INDEX Enter Help for help. @ COPY PAUSE SHUTDOWN @@ DEFINE PRINT SPOOL / DEL PROMPT SQLPLUS ACCEPT DESCRIBE QUIT START APPEND DISCONNECT RECOVER STARTUP ARCHIVE LOG EDIT REMARK STORE ATTRIBUTE EXECUTE REPFOOTER TIMING BREAK EXIT REPHEADER TTITLE ... COMPUTE LIST SET XQUERY CONNECT PASSWORD SHOW SQL> HELP CONNECT CONNECT ------ 47. What Information Is Needed to Connect SQL*Plus an Oracle Server? If you want to connect your SQL*Plus session to an Oracle server, you need to know the following information about this server: The network hostname, or IP address, of the Oracle server. The network port number where the Oracle server is listening for incoming connections. The name of the target database instance managed by the Oracle server. The name of your user account predefined on in the target database instance. The password of your user account predefined on in the target database instance. 48. What Is a Connect Identifier? A "connect identifier" is an identification string of a single set of connection information to a specific target database instance on a specific Oracle server. Connect identifiers are defined and stored in a file called tnsnames.ora located in $ORACLE_HOME/network/admin/ directory. Here is one example of a "connect identifier" definition: ggl_XE = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP) (HOST = www.atoztarget.com) (PORT = 1521) ) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = XE) ) ) The above "connect identifier" defines "TNS_XE" with the following connection information: * The network hostname: www.atoztarget.com. * The network port number: 1521. * The name of the target database instance: XE. 49. How To Connect a SQL*Plus Session to an Oracle Server? In order to connect a SQL*Plus session to an Oracle server, you need to: 1. Obtain the connection information from the Oracle server DBA. 2. Define a new "connect identifier" called "ggl_XE" in your tnsnames.org file with the given connection information. 3. Run the CONNECT command in SQL*Plus as shown in the tutorial exercise below: >cd c:oraclexeapporacleproduct10.2.0server >.binsqlplus /nolog SQL*Plus: Release 10.2.0.1.0 - Production on Tue ... Copyright (c) 1982, 2005, Oracle. All rights reserved. SQL> CONNECT ggl/retneclgg@ggl_XE; Connected. SQL> SELECT SYSDATE FROM DUAL; SYSDATE --------- 05-MAR-06 50. What Happens If You Use a Wrong Connect Identifier? Of course, you will get an error, if you use a wrong connect identifier. Here is an example of how SQL*Plus react to a wrong connect identifier: SQL> CONNECT ggl/retneclgg@WRONG; ERROR: ORA-12154: TNS:could not resolve the connect identifier specified Warning: You are no longer connected to ORACLE. What you need to do in this case: Check the CONNECT command to make sure that the connect identifier is entered correctly. Check the tnsnames.ora file to make sure that the connect identifier is defined correctly. Check the tnsnames.ora file to make sure that there is no multiple definitions of the same connect identifier. Check your files system to see if you have multiple copies of tnsnames.ora in different Oracle home directories, because you installed multiple versions of Oracle. If you do have multiple copies, make sure your SQL*Plus session is picking up the correct copy of tnsnames.ora. 51. What To Do If DBA Lost the SYSTEM Password? If the DBA lost the password of the SYSTEM user account, he/she can go to the Oracle server machine, and run SQL*Plus on server locally with the operating system authentication method to gain access to the database. The tutorial exercise below shows you how: (Terminal server to the Oracle server machine) (Start SQL*Plus) SQL>CONNECT / AS SYSDBA Connected. SQL> ALTER USER SYSTEM IDENTIFIED BY ssap_lgg; User altered. Notice that the (/) in the CONNECT command tells SQL*Plus to use the current user on local operating system as the connection authentication method. 52. What Types of Commands Can Be Executed in SQL*Plus? There are 4 types of commands you can run at the SQL*Plus command line prompt: 1. SQL commands - Standard SQL statements to be executed on target database on the Oracle server. For example: "SELECT * FROM ggl_faq;" is a SQL command. 2. PL/SQL commands - PL/SQL statements to be executed by the Oracle server. For example: "EXECUTE DBMS_OUTPUT.PUT_LINE('Welcome to www.atoztarget.com')" runs a PL/SQL command. SQL*Plus commands - Commands to be executed by the local SQL*Plus program itself. For example: "SET NULL 'NULL'" is a SQL*Plus command. OS commands - Commands to be executed by the local operating system. For example: "HOST dir" runs an operating system command on the local machine. 53. How To Run SQL Commands in SQL*Plus? If you want to run a SQL command in SQL*Plus, you need to enter the SQL command in one or more lines and terminated with (;). The tutorial exercise below shows a good example: SQL> SELECT 'Welcome!' FROM DUAL; 'WELCOME -------- Welcome! SQL> SELECT 'Welcome to atoztarget.com tutorials!' 2 FROM DUAL 3 ; 'WELCOMETOatoztarget.COMTUTORIALS!' ----------------------------------- Welcome to atoztarget.com tutorials! 54. How To Run PL/SQL Statements in SQL*Plus? If you want to run a single PL/SQL statement in SQL*Plus, you need to use the EXECUTE command as shown in the following tutorial example: SQL> SET SERVEROUTPUT ON SQL> EXECUTE DBMS_OUTPUT.PUT_LINE('Welcome to atoztarget!') Welcome to atoztarget! PL/SQL procedure successfully completed. 55. How To Change SQL*Plus System Settings? SQL*Plus environment is controlled a big list of SQL*Plus system settings. You can change them by using the SET command as shown in the following list: * SET AUTOCOMMIT OFF - Turns off the auto-commit feature. * SET FEEDBACK OFF - Stops displaying the "27 rows selected." message at the end of the query output. * SET HEADING OFF - Stops displaying the header line of the query output. * SET LINESIZE 256 - Sets the number of characters per line when displaying the query output. * SET NEWPAGE 2 - Sets 2 blank lines to be displayed on each page of the query output. * SET NEWPAGE NONE - Sets for no blank lines to be displayed on each page of the query output. * SET NULL 'null' - Asks SQL*Plus to display 'null' for columns that have null values in the query output. * SET PAGESIZE 60 - Sets the number of lines per page when displaying the query output. * SET TIMING ON - Asks SQL*Plus to display the command execution timing data. * SET WRAP OFF - Turns off the wrapping feature when displaying query output. 56. How To Look at the Current SQL*Plus System Settings? If you want to see the current values of SQL*Plus system settings, you can use the SHOW command as shown in the following tutorial exercise: SQL> SHOW AUTOCOMMIT autocommit OFF SQL> SHOW HEADING heading ON SQL> SHOW LINESIZE linesize 80 SQL> SHOW PAGESIZE pagesize 14 SQL> SHOW FEEDBACK FEEDBACK ON for 6 or more rows SQL> SHOW TIMING timing OFF SQL> SHOW NULL null "" SQL> SHOW ALL appinfo is OFF and set to "SQL*Plus" arraysize 15 autocommit OFF autoprint OFF autorecovery OFF autotrace OFF blockterminator "." (hex 2e) cmdsep OFF colsep " " compatibility version NATIVE concat "." (hex 2e) copycommit 0 COPYTYPECHECK is ON define "&" (hex 26) describe DEPTH 1 LINENUM OFF INDENT ON echo OFF 57. What Are SQL*Plus Environment Variables? Behaviors of SQL*Plus are also controlled a some environment variables predefined on the local operating system. Here are some commonly used SQL*Plus environment variables: * ORACLE_HOME - The home directory where your Oracle client application is installed. * PATH - A list of directories where SQL*Plus will search for executable or DLL files. PATH should include $ORACLE_HOMEbin. * SQLPLUS - The directory where localization messages are stored. SQLPLUS should be set to $ORACLE_HOMEsqlplusmesg * TNS_ADMIN - The directory where the connect identifier file, tnsnames.ora is located. TNS_ADMIN should be set to $ORACLE_HOME/network/admin. 58. How To Generate Query Output in HTML Format? If you want your query output to be generated in HTML format, you can use the "SET MARKUP HTML ON" to turn on the HTML feature. The following tutorial exercise gives you a good example: SQL> connect HR/retneclgg SQL> SET MARKUP HTML ON SQL> SELECT FIRST_NAME, LAST_NAME, HIRE_DATE 2 FROM EMPLOYEES WHERE FIRST_NAME LIKE 'Joh%'; FIRST_NAME LAST_NAME HIRE_DATE John Seo 12-FEB-98 John Russell 01-OCT-96 59. What Is Output Spooling in SQL*Plus? The output spooling a nice feature of the command-line SQL*Plus tool. If the spooling feature is turned on, SQL*Plus will send a carbon copy of the everything on your screen to a specified local file. Output spooling is used mostly for quick dump of data to local files. Here are the commands to turn on and off output spooling in SQL*Plus: * SPOOL fileName - Turning on output spooling with the specified file. * SPOOL OFF - Turning off output spooling and close the spool file. 60. How To Save Query Output to a Local File? Normally, when you run a SELECT statement in SQL*Plus, the output will be displayed on your screen. If you want the output to be saved to local file, you can use the "SPOOL fileName" command to specify a local file and start the spooling feature. When you are done with your SELECT statement, you need to close the spool file with the "SPOOL OFF" command. The following tutorial exercise gives you a good example: SQL> connect HR/retneclgg SQL> SET HEADING OFF SQL> SET FEEDBACK OFF SQL> SET LINESIZE 1000 SQL> SPOOL tempemployees.lst SQL> SELECT * FROM EMPLOYEES; ...... SQL> SPOOL OFF You should get all records in employees.lst with fixed length fields. 61. What Is Input Buffer in SQL*Plus? Input buffer is a nice feature of the command-line SQL*Plus tool. It allows you to revise a multiple-line command and re-run it with a couple of simple commands. By default, input buffer is always turned on in SQL*Plus. The last SQL statement is always stored in the buffer. All you need is to remember to following commonly used commands: * LIST - Displays the SQL statement (the last executed SQL statement) in the buffer. * RUN - Runs the SQL statement in the buffer again. ";" is a quick command equivalent to RUN. * CLEAR BUFFER - Removes the SQL statement in the buffer. * INPUT line - Adds a new line into the buffer. * APPEND text - Appends more text to the last line in the buffer. * DEL - Deletes one line from the buffer. * CHANGE /old/new - Replaces 'old' text with 'new' text in the buffer. 62. How To Revise and Re-Run the Last SQL Command? If executed a long SQL statement, found a mistake in the statement, and you don't want enter that long statement again, you can use the input buffer commands to the correct last statement and re-run it. The following tutorial exercise gives you a good example: SQL> connect HR/retneclgg SQL> SELECT FIRST_NAME, LAST_NAME, HIRE_DATE 2 FROM EMPLOYEE WHERE FIRST_NAME LIKE 'Joh%'; FROM EMPLOYEE WHERE FIRST_NAME LIKE 'Joh%' * ERROR at line 2: ORA-00942: table or view does not exist SQL> LIST 1 SELECT FIRST_NAME, LAST_NAME, HIRE_DATE 2* FROM EMPLOYEES WHERE FIRST_NAME LIKE 'Joh%' SQL> CHANGE /EMPLOYEE/EMPLOYEES/ 2* FROM EMPLOYEES WHERE FIRST_NAME LIKE 'Joh%' SQL> RUN (Query output) SQL> INPUT ORDER BY FIRE_DATE SQL> LIST 1 SELECT FIRST_NAME, LAST_NAME, HIRE_DATE 2 FROM EMPLOYEE WHERE FIRST_NAME LIKE 'Joh%' 3* ORDER BY HIRE_DATE SQL> RUN (Query output) SQL> CLEAR BUFFER buffer cleared SQL> LIST SP2-0223: No lines in SQL buffer. 63. How Run SQL*Plus Commands That Are Stored in a Local File? If you have a group of commands that you need to run them repeatedly every day, you can save those commands in a file (called SQL script file), and using the "@fileName" command to run them in SQL*Plus. If you want to try this, create a file called tempinput.sql with: SELECT 'Welcome to' FROM DUAL; SELECT 'atoztarget.com!' FROM DUAL; Then run the "@" command in SQL*Plus as: SQL> connect HR/retneclgg SQL> @tempinput.sql 'WELCOMETO ---------- Welcome to 'atoztarget.COM -------------- atoztarget.com! 64. How To Use SQL*Plus Built-in Timers? If you don't have a stopwatch/timer and want to measure elapsed periods of time, you can SQL*Plus Built-in Timers with the following commands: * TIMING - Displays number of timers. * TIMING START - Starts a new timer with or without a name. * TIMING SHOW - Shows the current time of the named or not-named timer. * TIMING STOP - Stops the named or not-named timer. The following tutorial exercise shows you a good example of using SQL*Plus built-in timers: SQL> TIMING START timer_1 (some seconds later) SQL> TIMING START timer_2 (some seconds later) SQL> TIMING START timer_3 (some seconds later) SQL> TIMING SHOW timer_1 timing for: timer_2 Elapsed: 00:00:19.43 (some seconds later) SQL> TIMING STOP timer_2 timing for: timer_2 Elapsed: 00:00:36.32 SQL> TIMING 2 timing elements in use 65. What Is Oracle Server Autotrace in Oracle? Autotrace is Oracle server feature that generates two statement execution reports very useful for performance tuning: * Statement execution path - Shows you the execution loop logic of a DML statement. * Statement execution statistics - Shows you various execution statistics of a DML statement. To turn on the autotrace feature, the Oracle server DBA need to: * Create a special table called PLAN_TABLE. * Create a special security role called PLUSTRACE. * Grant PLUSTRACE role your user account. 66. How To Set Up Autotrace for a User Account? If an Oracle user wants to use the autotrace feature, you can use the tutorial as an example to create the required table PLAN_TABLE, the required security role PLUSTRACE, and grant the role to that user: SQL> CONNECT HR/retneclgg SQL> @oraclexeapporacleproduct10.2.0server RDBMSADMINUTLXPLAN.SQL Table (HR.PLAN_TABLE) created. SQL> CONNECT / AS SYSDBA SQL> @C:oraclexeapporacleproduct10.2.0server SQLPLUSADMINPLUSTRCE.SQL SQL> drop role plustrace; Role (PLUSTRACE) dropped. SQL> create role plustrace; Role (PLUSTRACE) created. SQL> grant plustrace to dba with admin option; Grant succeeded. SQL> GRANT PLUSTRACE TO HR; Grant succeeded. Remember that PLAN_TABLE table must be created under the user schema HR. 67. How To Get Execution Path Reports on Query Statements? If your user account has autotrace configured by the DBA, you can use the "SET AUTOTRACE ON EXPLAIN" command to turn on execution path reports on query statements. The tutorial exercise bellow shows you a good example: SQL> CONNECT HR/retneclgg SQL> SET AUTOTRACE ON EXPLAIN SQL> SELECT E.LAST_NAME, E.SALARY, J.JOB_TITLE 2 FROM EMPLOYEES E, JOBS J 3 WHERE E.JOB_ID=J.JOB_ID AND E.SALARY>12000; LAST_NAME SALARY JOB_TITLE ----------------- ---------- ----------------------------- King 24000 President Kochhar 17000 Administration Vice President De Haan 17000 Administration Vice President Russell 14000 Sales Manager Partners 13500 Sales Manager Hartstein 13000 Marketing Manager 6 rows selected. 68. How To Get Execution Statistics Reports on Query Statements? If your user account has autotrace configured by the DBA, you can use the "SET AUTOTRACE ON STATISTICS" command to turn on execution statistics reports on query statements. The tutorial exercise bellow shows you a good example: SQL> CONNECT HR/retneclgg SQL> SET AUTOTRACE ON STATISTICS SQL> SELECT E.LAST_NAME, E.SALARY, J.JOB_TITLE 2 FROM EMPLOYEES E, JOBS J 3 WHERE E.JOB_ID=J.JOB_ID AND E.SALARY>12000; LAST_NAME SALARY JOB_TITLE ----------------- ---------- ----------------------------- King 24000 President Kochhar 17000 Administration Vice President De Haan 17000 Administration Vice President Russell 14000 Sales Manager Partners 13500 Sales Manager Hartstein 13000 Marketing Manager 6 rows selected. 69. What Is SQL in Oracle? SQL, SEQUEL (Structured English Query Language), is a language for RDBMS (Relational Database Management Systems). SQL was developed by IBM Corporation. 70. How Many Categories of Data Types in Oracle? Oracles supports the following categories of data types: * Oracle Built-in Datatypes. * ANSI, DB2, and SQL/DS Datatypes. * User-Defined Types. * Oracle-Supplied Types. 71. What Are the Oracle Built-in Data Types? There are 20 Oracle built-in data types, divided into 6 groups: * Character Datatypes - CHAR, NCHAR, NVARCHAR2, VARCHAR2 * Number Datatypes - NUMBER, BINARY_FLOAT, BINARY_DOUBLE * Long and Row Datatypes - LONG, LONG RAW, RAW * Datetime Datatypes - DATE, TIMESTAMP, INTERVAL YEAR TO MONTH, INTERVAL DAY TO SECOND * Large Object Datatypes - BLOB, CLOB, NCLOB, BFILE * Row ID Datatypes - ROWID, UROWID 72. What Are the Differences between CHAR and NCHAR in Oracle? Both CHAR and NCHAR are fixed length character data types. But they have the following differences: * CHAR's size is specified in bytes by default. * NCHAR's size is specified in characters by default. A character could be 1 byte to 4 bytes long depending on the character set used. * NCHAR stores characters in Unicode. 73. What Are the Differences between CHAR and VARCHAR2 in Oracle? The main differences between CHAR and VARCHAR2 are: * CHAR stores values in fixed lengths. Values are padded with space characters to match the specified length. * VARCHAR2 stores values in variable lengths. Values are not padded with any characters. 74. What Are the Differences between NUMBER and BINARY_FLOAT in Oracle? The main differences between NUMBER and BINARY_FLOAT in Oracle are: * NUMBER stores values as fixed-point numbers using 1 to 22 bytes. * BINARY_FLOAT stores values as single precision floating-point numbers. 75. What Are the Differences between DATE and TIMESTAMP in Oracle? The main differences between DATE and TIMESTAMP in Oracle are: * DATE stores values as century, year, month, date, hour, minute, and second. * TIMESTAMP stores values as year, month, day, hour, minute, second, and fractional seconds. 76. What Are the Differences between INTERVAL YEAR TO MONTH and INTERVAL DAY TO SECOND? The main differences between INTERVAL YEAR TO MONTH and INTERVAL DAY TO SECOND are: * INTERVAL YEAR TO MONTH stores values as time intervals at the month level. * INTERVAL DAY TO SECOND stores values as time intervals at the fractional seconds level. 77. What Are the Differences between BLOB and CLOB in Oracle? The main differences between BLOB and CLOB in Oracle are: * BLOB stores values as LOB (Large OBject) in bitstreams. * CLOB stores values as LOB (Large OBject) in character steams. 78. What Are the ANSI Data Types Supported in Oracle? The following ANSI data types are supported in Oracle: * CHARACTER(n) / CHAR(n) * CHARACTER VARYING(n) / CHAR VARYING(n) * NATIONAL CHARACTER(n) / NATIONAL CHAR(n) / NCHAR(n) * NATIONAL CHARACTER VARYING(n) / NATIONAL CHAR VARYING(n) / NCHAR VARYING(n) * NUMERIC(p,s) * DECIMAL(p,s) * INTEGER / INT * SMALLINT * FLOAT * DOUBLE PRECISION * REAL 79. How To Write Text Literals in Oracle? There are several ways to write text literals as shown in the following samples: SELECT 'atoztarget.com' FROM DUAL -- The most common format atoztarget.com SELECT 'It''s Sunday!' FROM DUAL -- Single quote escaped It's Sunday! SELECT N'Allo, C''est moi.' FROM DUAL -- National chars Allo, C'est moi. SELECT Q'/It's Sunday!/' FROM DUAL -- Your own delimiter It's Sunday! 80. How To Write Numeric Literals in Oracle? Numeric literals can coded as shown in the following samples: SELECT 255 FROM DUAL -- An integer 255 SELECT -6.34 FROM DUAL -- A regular number -6.34 SELECT 2.14F FROM DUAL -- A single-precision floating point 2.14 SELECT -0.5D FROM DUAL -- A double-precision floating point -0.5 81. How To Write Date and Time Literals in Oracle? Date and time literals can coded as shown in the following samples: SELECT DATE '2002-10-03' FROM DUAL -- ANSI date format 03-OCT-07 SELECT TIMESTAMP '0227-01-31 09:26:50.124' FROM DUAL 31-JAN-07 09.26.50.124000000 AM -- This is ANSI format 82. How To Write Date and Time Interval Literals in Oracle? Date and time interval literals can coded as shown in the following samples: SELECT DATE '2002-10-03' + INTERVAL '123-2' YEAR(3) TO MONTH FROM DUAL -- 123 years and 2 months is added to 2002-10-03 03-DEC-25 SELECT DATE '2002-10-03' + INTERVAL '123' YEAR(3) FROM DUAL -- 123 years is added to 2002-10-03 03-OCT-25 SELECT DATE '2002-10-03' + INTERVAL '299' MONTH(3) FROM DUAL -- 299 months years is added to 2002-10-03 03-SEP-27 SELECT TIMESTAMP '1997-01-31 09:26:50.124' + INTERVAL '4 5:12:10.222' DAY TO SECOND(3) FROM DUAL 04-FEB-97 02.39.00.346000000 PM SELECT TIMESTAMP '1997-01-31 09:26:50.124' + INTERVAL '4 5:12' DAY TO MINUTE FROM DUAL 04-FEB-97 02.38.50.124000000 PM SELECT TIMESTAMP '1997-01-31 09:26:50.124' + INTERVAL '400 5' DAY(3) TO HOUR FROM DUAL 07-MAR-98 02.26.50.124000000 PM SELECT TIMESTAMP '1997-01-31 09:26:50.124' + INTERVAL '400' DAY(3) FROM DUAL 07-MAR-98 09.26.50.124000000 AM SELECT TIMESTAMP '1997-01-31 09:26:50.124' + INTERVAL '11:12:10.2222222' HOUR TO SECOND(7) FROM DUAL 31-JAN-97 08.39.00.346222200 PM 83. How To Convert Numbers to Characters in Oracle? You can convert numeric values to characters by using the TO_CHAR() function as shown in the following examples: SELECT TO_CHAR(4123.4570) FROM DUAL 123.457 SELECT TO_CHAR(4123.457, '$9,999,999.99') FROM DUAL $4,123.46 SELECT TO_CHAR(-4123.457, '9999999.99EEEE') FROM DUAL -4.12E+03 84. How To Convert Characters to Numbers in Oracle? You can convert characters to numbers by using the TO_NUMBER() function as shown in the following examples: SELECT TO_NUMBER('4123.4570') FROM DUAL 4123.457 SELECT TO_NUMBER(' $4,123.46','$9,999,999.99') FROM DUAL 4123.46 SELECT TO_NUMBER(' -4.12E+03') FROM DUAL -4120 85. How To Convert Dates to Characters in Oracle? You can convert dates to characters using the TO_CHAR() function as shown in the following examples: SELECT TO_CHAR(SYSDATE, 'DD-MON-YYYY') FROM DUAL; -- SYSDATE returns the current date 07-MAY-2006 SELECT TO_CHAR(SYSDATE, 'YYYY/MM/DD') FROM DUAL; 2006/05/07 SELECT TO_CHAR(SYSDATE, 'MONTH DD, YYYY') FROM DUAL; MAY 07, 2006 SELECT TO_CHAR(SYSDATE, 'fmMONTH DD, YYYY') FROM DUAL; May 7, 2006 SELECT TO_CHAR(SYSDATE, 'fmDAY, MONTH DD, YYYY') FROM DUAL; SUNDAY, MAY 7, 2006 86. How To Convert Characters to Dates in Oracle? You can convert dates to characters using the TO_DATE() function as shown in the following examples: SELECT TO_DATE('07-MAY-2006', 'DD-MON-YYYY') FROM DUAL; 07-MAY-06 SELECT TO_DATE('2006/05/07 ', 'YYYY/MM/DD') FROM DUAL; 07-MAY-06 SELECT TO_DATE('MAY 07, 2006', 'MONTH DD, YYYY') FROM DUAL; 07-MAY-06 SELECT TO_DATE('May 7, 2006', 'fmMONTH DD, YYYY') FROM DUAL; 07-MAY-06 SELECT TO_DATE('SUNDAY, MAY 7, 2006', 'fmDAY, MONTH DD, YYYY') FROM DUAL; 07-MAY-06 87. How To Convert Times to Characters in Oracle? You can convert dates to characters using the TO_CHAR() function as shown in the following examples: SELECT TO_CHAR(SYSDATE, 'HH:MI:SS') FROM DUAL; 04:49:49 SELECT TO_CHAR(SYSDATE, 'HH24:MI:SS.FF') FROM DUAL; -- Error: SYSDATE has no fractional seconds SELECT TO_CHAR(SYSTIMESTAMP, 'HH24:MI:SS.FF9') FROM DUAL; 16:52:57.847000000 SELECT TO_CHAR(SYSDATE, 'SSSSS') FROM DUAL; -- Seconds past midnight 69520 88. How To Convert Characters to Times in Oracle? You can convert dates to characters using the TO_CHAR() function as shown in the following examples: SELECT TO_CHAR(TO_DATE('04:49:49', 'HH:MI:SS'), 'DD-MON-YYYY HH24:MI:SS') FROM DUAL; -- Default date is the first day of the current month 01-MAY-2006 04:49:49 SELECT TO_CHAR(TO_TIMESTAMP('16:52:57.847000000', 'HH24:MI:SS.FF9'), 'DD-MON-YYYY HH24:MI:SS.FF9') FROM DUAL; 01-MAY-2006 16:52:57.847000000 SELECT TO_CHAR(TO_DATE('69520', 'SSSSS'), 'DD-MON-YYYY HH24:MI:SS') FROM DUAL; 01-MAY-2006 19:18:40 89. What Is NULL value in Oracle? NULL is a special value representing "no value" in all data types. NULL can be used on in operations like other values. But most operations has special rules when NULL is involved. The tutorial exercise below shows you some examples: SET NULL 'NULL'; -- Make sure NULL is displayed SELECT NULL FROM DUAL; N - N U L L SELECT NULL + NULL FROM DUAL; NULL+NULL ---------- NULL SELECT NULL + 7 FROM DUAL; NULL+7 ---------- NULL SELECT NULL * 7 FROM DUAL; NULL*7 ---------- NULL SELECT NULL || 'A' FROM DUAL; N - A SELECT NULL + SYSDATE FROM DUAL; NULL+SYSD --------- NULL 90. How To Use NULL as Conditions in Oracle? If you want to compare values against NULL as conditions, you should use the "IS NULL" or "IS NOT NULL" operator. Do not use "=" or "" against NULL. The sample script below shows you some good examples: SELECT 'A' IS NULL FROM DUAL; -- Error: Boolean is not data type. -- Boolean can only be used as conditions SELECT CASE WHEN 'A' IS NULL THEN 'TRUE' ELSE 'FALSE' END FROM DUAL; FALSE SELECT CASE WHEN '' IS NULL THEN 'TRUE' ELSE 'FALSE' END FROM DUAL; TRUE SELECT CASE WHEN 0 IS NULL THEN 'TRUE' ELSE 'FALSE' END FROM DUAL; FALSE SELECT CASE WHEN NULL IS NULL THEN 'TRUE' ELSE 'FALSE' END FROM DUAL; TRUE SELECT CASE WHEN 'A' = NULL THEN 'TRUE' ELSE 'FALSE' END FROM DUAL; -- Do not use "=" FALSE SELECT CASE WHEN 'A' NULL THEN 'TRUE' ELSE 'FALSE' END FROM DUAL; -- Do not use "" FALSE SELECT CASE WHEN NULL = NULL THEN 'TRUE' ELSE 'FALSE' END FROM DUAL; -- Do not use "=" FALSE 91. How To Concatenate Two Text Values in Oracle? There are two ways to concatenate two text values together: * CONCAT() function. * '||' operation. Here is some examples on how to use them: SELECT 'ggl' || 'Center' || '.com' FROM DUAL; atoztarget.com SELECT CONCAT('atoztarget','.com') FROM DUAL; atoztarget.com 92. How To Increment Dates by 1 in Oracle? If you have a date, and you want to increment it by 1. You can do this by adding the date with a date interval. You can also do this by adding the number 1 directly on the date. The tutorial example below shows you how to adding numbers to dates, and take date differences: SELECT TO_DATE('30-APR-06') + 1 FROM DUAL; -- Adding 1 day to a date 01-MAY-06 SELECT TO_DATE('01-MAY-06') - TO_DATE('30-APR-06') FROM DUAL; -- Taking date differences 1 SELECT SYSTIMESTAMP + 1 FROM DUAL; -- The number you add is always in days. 08-MAY-06 SELECT TO_CHAR(SYSTIMESTAMP+1,'DD-MON-YYYY HH24:MI:SS.FF3') FROM DUAL; -- Error: Adding 1 to a timestamp makes it a date. 93. How To Calculate Date and Time Differences in Oracle? If you want to know how many years, months, days and seconds are there between two dates or times, you can use the date and time interval expressions: YEAR ... TO MONTH and DAY ... TO SECOND. The tutorial exercise below gives you some good examples: SELECT (TO_DATE('01-MAY-2006 16:52:57','DD-MON-YYYY HH24:MI:SS') - TO_DATE('31-JAN-1897 09:26:50','DD-MON-YYYY HH24:MI:SS')) YEAR(4) TO MONTH FROM DUAL; -- 109 years and 3 months 109-3 SELECT (TO_DATE('01-MAY-2006 16:52:57','DD-MON-YYYY HH24:MI:SS') - TO_DATE('31-JAN-1897 09:26:50','DD-MON-YYYY HH24:MI:SS')) DAY(9) TO SECOND FROM DUAL; -- 39901 days and some seconds 39901 7:26:7.0 SELECT (TO_TIMESTAMP('01-MAY-2006 16:52:57.847', 'DD-MON-YYYY HH24:MI:SS.FF3') - TO_TIMESTAMP('31-JAN-1897 09:26:50.124', 'DD-MON-YYYY HH24:MI:SS.FF3')) YEAR(4) TO MONTH FROM DUAL; -- 109 years and 3 months 109-3 SELECT (TO_TIMESTAMP('01-MAY-2006 16:52:57.847', 'DD-MON-YYYY HH24:MI:SS.FF3') - TO_TIMESTAMP('31-JAN-1897 09:26:50.124','DD-MON-YYYY HH24:MI:SS.FF3')) DAY(9) TO SECOND FROM DUAL; -- 39 94. How To Use IN Conditions in Oracle? An IN condition is single value again a list of values. It returns TRUE, if the specified value is in the list. Otherwise, it returns FALSE. Some examples are given in the script below: SELECT CASE WHEN 3 IN (1,2,3,5) THEN 'TRUE' ELSE 'FALSE' END FROM DUAL; TRUE SELECT CASE WHEN 3 NOT IN (1,2,3,5) THEN 'TRUE' ELSE 'FALSE' END FROM DUAL; FALSE SELECT CASE WHEN 'Y' IN ('F','Y','I') THEN 'TRUE' ELSE 'FALSE' END FROM DUAL; TRUE 95. How To Use LIKE Conditions in Oracle? LIKE condition is also called pattern patch. There 3 main rules on using LIKE condition: * '_' is used in the pattern to match any one character. * '%' is used in the pattern to match any zero or more characters. * ESCAPE clause is used to provide the escape character in the pattern. The following script provides you some good pattern matching examples: SELECT CASE WHEN 'atoztarget.com' LIKE '%Center%' THEN 'TRUE' ELSE 'FALSE' END FROM DUAL; TRUE SELECT CASE WHEN 'atoztarget.com' LIKE '%CENTER%' THEN 'TRUE' ELSE 'FALSE' END FROM DUAL; -- Case sensitive by default FALSE SELECT CASE WHEN 'atoztarget.com' LIKE '%Center_com' THEN 'TRUE' ELSE 'FALSE' END FROM DUAL; TRUE SELECT CASE WHEN '100% correct' LIKE '100% %' ESCAPE '' THEN 'TRUE' ELSE 'FALSE' END FROM DUAL; TRUE 96. How To Use Regular Expression in Pattern Match Conditions in Oracle? If you have a pattern that is too complex for LIKE to handle, you can use the regular expression pattern patch function: REGEXP_LIKE(). The following script provides you some good examples: SELECT CASE WHEN REGEXP_LIKE ('atoztarget.com', '.*ggl.*','i') THEN 'TRUE' ELSE 'FALSE' END FROM DUAL; TRUE SELECT CASE WHEN REGEXP_LIKE ('atoztarget.com', '.*com$','i') THEN 'TRUE' ELSE 'FALSE' END FROM DUAL; TRUE SELECT CASE WHEN REGEXP_LIKE ('atoztarget.com', '^F.*','i') THEN 'TRUE' ELSE 'FALSE' END FROM DUAL; TRUE 97. What Are DDL Statements in Oracle? DDL (Data Definition Language) statements are statements to create and manage data objects in the database. The are 3 primary DDL statements: CREATE - Creating a new database object. ALTER - Altering the definition of an existing data object. DROP - Dropping an existing data object. 98. How To Create a New Table in Oracle? If you want to create a new table in your own schema, you can log into the server with your account, and use the CREATE TABLE statement. The following script shows you how to create a table: >.binsqlplus /nolog SQL> connect HR/atoztarget Connected. SQL> CREATE TABLE tip (id NUMBER(5) PRIMARY KEY, subject VARCHAR(80) NOT NULL, description VARCHAR(256) NOT NULL, create_date DATE DEFAULT (sysdate)); Table created. This scripts creates a testing table called "tip" with 4 columns in the schema associated with the log in account "HR". 99. How To Create a New Table by Selecting Rows from Another Table? Let's say you have a table with many data rows, now you want to create a backup copy of this table of all rows or a subset of them, you can use the CREATE TABLE...AS SELECT statement to do this. Here is an example script: >.binsqlplus /nolog SQL> connect HR/atoztarget Connected. SQL> CREATE TABLE emp_dept_10 2 AS SELECT * FROM employees WHERE department_id=10; Table created. SQL> SELECT first_name, last_name, salary 2 FROM emp_dept_10; FIRST_NAME LAST_NAME SALARY -------------------- ------------------------- ---------- Jennifer Whalen 4400 As you can see, this SQL scripts created a table called "emp_dept_10" using the same column definitions as the "employees" table and copied data rows of one department. This is really a quick and easy way to create a table. 100. How To Add a New Column to an Existing Table in Oracle? If you have an existing table with existing data rows, and want to add a new column to that table, you can use the ALTER TABLE ... ADD statement to do this. Here is an example script: SQL> connect HR/atoztarget Connected. SQL> CREATE TABLE emp_dept_110 2 AS SELECT * FROM employees WHERE department_id=110; Table created. SQL> ALTER TABLE emp_dept_110 ADD (vacation NUMBER); Table altered. SQL> SELECT first_name, last_name, vacation 2 FROM emp_dept_110; FIRST_NAME LAST_NAME VACATION -------------------- ------------------------- ---------- Shelley Higgins William Gietz This SQL script added a new column called "vacation" to the "emp_dept_110" table. NULL values were added to this column on all existing data rows. ORACLE Database Questions and Answers Pdf Download Read the full article

[eswars-tech-world]

Question-99: What is the purpose of the DBMS_MVIEW package in Oracle, and how do you use it for managing materialized views?

Interview Questions on Oracle SQL & PLSQL Development: For more questions like this: Do follow the main blog #oracledatabase #interviewquestions #freshers #beginners #intermediatelevel #experienced #eswarstechworld #oracle #interview #development #sql

The DBMS_MVIEW package in Oracle is used for managing materialized views. A materialized view is a database object that contains the results of a query. It is a precomputed and stored version of a query’s result set. The DBMS_MVIEW package provides procedures to create, manage, and refresh materialized views in Oracle. Categories: The DBMS_MVIEW package offers procedures for various operations…